如何计算 Oracle SQL 中计数列的总和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20572082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I compute the sum of a count column in Oracle SQL?
提问by Dan
I am trying to compute the sum of a column which counts values of another number column. The specific column I want to compute the sum of is 'COUNT(d.totalcostofrent).
我正在尝试计算对另一个数字列的值进行计数的列的总和。我要计算总和的特定列是 'COUNT(d.totalcostofrent)。
I just can't get the sum to show up under the table at all!
我根本无法让总和出现在桌子底下！
Here is my code:
这是我的代码：
COLUMN dummy NOPRINT
COMPUTE SUM OF totalcostofrent ON DUMMY
BREAK ON dummy
COLUMN branchname HEADING 'Branch Name'
COLUMN "Revenue" HEADING 'Rental Revenue' FORMAT ,999
select null dummy,
a.branchname,
count(c.rentalid) "Number of Rentals",
SUM(d.totalcostofrent) "Revenue"
from branch a
inner join branchstaffrole b on a.branchid = b.branchid
inner join rental c on b.staffid = c.branchstaffid
inner join rentaltool d on c.rentalid = d.rentalid
where to_char(d.DATEOUT, 'YYYY') = '&Year'
group by a.branchname
order by 4 DESC;
Branch Name Number of Rentals Rental Revenue
-------------------- ----------------- --------------
Lichfield 2 0
Stoke-on-Trent 3 0
Birmingham 3 5
Stafford 3 5
I would like it to look like this:
我希望它看起来像这样：
Branch Name Number of Rentals Rental Revenue
-------------------- ----------------- --------------
Lichfield 2 0
Stoke-on-Trent 3 0
Birmingham 3 5
Stafford 3 5
____
Sum 0
Any suggestions would be greatly appreciated!
任何建议将不胜感激！
回答by Craig
It looks like you are trying to format the report with SQL*Plus commands. You are really close, only you have an alias for the column: SUM(d.totalcostofrent) "Revenue". So you need to use that alias in your Compute SQL*Plus command as well:
看起来您正在尝试使用 SQL*Plus 命令格式化报告。你真的很接近，只有你有一个列的别名：SUM(d.totalcostofrent)“收入”。因此，您还需要在 Compute SQL*Plus 命令中使用该别名：
COMPUTE SUM OF "Revenue" ON DUMMY
This will return:
这将返回：
Branch Name Number of Rentals Rental Revenue
-------------- ----------------- --------------
Lichfield 2 0
Birmingham 3 5
Stoke-on-Trent 3 0
Stafford 3 5
--------------
0
You can also do the same excluding the dummy column with:
您还可以执行相同的操作，排除虚拟列：
COMPUTE SUM OF "Revenue" ON REPORT
BREAK ON REPORT
COLUMN branchname HEADING 'Branch Name'
COLUMN "Revenue" HEADING 'Rental Revenue' FORMAT ,999
Which returns:
返回：
Branch Name Number of Rentals Rental Revenue
-------------- ----------------- --------------
Lichfield 2 0
Birmingham 3 5
Stoke-on-Trent 3 0
Stafford 3 5
--------------
sum 0
回答by dg99
Use the ROLLUPextension to extend the aggregation function (e.g. count) to higher level groups:
使用ROLLUP扩展将聚合函数（例如count）扩展到更高级别的组：
group by rollup(a.branchname)
See this reference.
请参阅此参考资料。
