oracle 显示每条记录的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1947523/
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
Show sum of all for every record
提问by ark
Say you have table of some items with these two columns:
假设您有包含这两列的一些项目的表格：
- ItemName
- Price
- 项目名称
- 价钱
....where ItemNameis unique.
....哪里ItemName是独一无二的。
How do you show sum of all prices for every ItemName(I'm using Oracle)? If I just try this:
您如何显示每个ItemName（我使用的是 Oracle）的所有价格的总和？如果我只是试试这个：
SELECT ItemName,
SUM(Price)
FROM Items
I get this error:
我收到此错误：
ORA-00937: not a single-group group function
ORA-00937: 不是单组组函数
...which goes away only if I use GROUP BY. But then I can SUM only by groups, not all of them.
...只有当我使用 GROUP BY 时它才会消失。但是我只能按组求和，而不是所有的求和。
回答by Quassnoi
SELECT ItemName, SUM(Price) OVER()
FROM Items
This query will return you the list of ItemName's along with the total sum of prices against each ItemName:
此查询将返回ItemName's列表以及每个 s 的总价格ItemName：
SELECT ItemName, SUM(Price) OVER()
FROM (
SELECT 'Apple' AS ItemName, 100 AS price
FROM dual
UNION ALL
SELECT 'Banana', 200 AS price
FROM dual
UNION ALL
SELECT 'Cocoa', 300 AS price
FROM dual
) q
ItemName Price
-------- -----
Apple 600
Banana 600
Cocoa 600
回答by Guffa
You can't both group and not group in the same query. You can use a subquery to get the price:
您不能在同一个查询中既分组又不分组。您可以使用子查询来获取价格：
select ItemName, (select sum(Price) from Items) as AllPrices
from Items
As the subquery doesn't use any data from the records in the main query, the database should be able to optimise it to only execute the subquery once.
由于子查询不使用主查询中记录的任何数据，数据库应该能够优化它以只执行一次子查询。
回答by Kaleb Brasee
If ItemName is unique, won't the Price for each ItemName be whatever is associated with that ItemName?
如果 ItemName 是唯一的，那么每个 ItemName 的价格不是与该 ItemName 相关联的任何内容吗？
If you're looking for the sum of allItem prices, you would have to leave out the ItemName from your SQL:
如果您要查找所有Item 价格的总和，则必须从 SQL 中省略 ItemName：
SELECT SUM(Price) FROM Items
回答by Paul Tomblin
If you want the sum of all of them, why are you including ItemName in your query? You have two choices, include the ItemName and get the sum of all prices for that ItemName using "GROUP BY ItemName", or you can get the sum of all items, in which case you drop the "ItemName," from the select.
如果您想要所有这些的总和，为什么要在查询中包含 ItemName？您有两种选择，包括 ItemName 并使用“GROUP BY ItemName”获取该 ItemName 的所有价格总和，或者您可以获得所有项目的总和，在这种情况下，您可以从选择中删除“ItemName”。
回答by James Keesey
Have you tried
你有没有尝试过
SELECT SUM(Price) FROM Items
回答by Dave Swersky
If ItemName is unique, then the aggregated result will contain the same number of rows as the table. The SUM function will group by a column where duplicates exist to give you a SUM for all columns with that value:
如果 ItemName 是唯一的，则聚合结果将包含与表相同的行数。SUM 函数将按存在重复项的列分组，为您提供具有该值的所有列的 SUM：
SELECT ItemName, SUM(Price)
FROM [Table]
GROUP BY ItemName
Table Values:
表值：
ItemName Price
======== =====
Foo .00
Bar .00
Bar .00
Aggregate Result:
汇总结果：
ItemName Price
======== =====
Foo .00
Bar .00
回答by David Oneill
It wants to you do:
它要你做：
select ItemName, sum(price)
from table
group by ItemName;
If you want the sum of ALL the items, what is the meaning of having the ItemName reported?
如果您想要所有项目的总和，那么报告 ItemName 是什么意思？
table:
桌子：
item price
spoon 2
spoon 4
fork 3
plate 6
For the above table, the sum is 15. What is the item name of the sum?
对于上表，总和为15。总和的项目名称是什么？
回答by pdavis
Since ItemName is unique, you need to use the following...
由于 ItemName 是唯一的，因此您需要使用以下...
SELECT SUM(Price) FROM Items
回答by Jeffrey Kemp
Perhaps what you're after is a list of items, their prices, as well as a Total of the prices?
也许您想要的是物品清单、它们的价格以及总价格？
SELECT ItemName, SUM(Price) Price
FROM Items
GROUP BY ROLLUP(ItemName);
ITEMNAME PRICE
======== =====
Apples 1.00
Bananas 2.00
Cherries 3.00
6.00
