具有不同和总和的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15512628/
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
SQL query with distinct and sum
提问by Andrey
I have the following medleystable that combines colors, fruitsand ratings:
我有下medleys表结合了colors,fruits和ratings：
[medleys]
medley_id | color | fruit | rating
==============================================
1 red apple 25
2 blue pear 5
3 green apple 12
4 red apple 10
5 purple kiwi 5
6 purple kiwi 50
7 blue kiwi 3
8 blue pear 9
I am trying to write an ANSI-compliant SQL query that will combine every unique/distinct color-fruitpair and sum each pair's individual ratingvalues. Thus if you ran the query on the table above it would produce the following result sets:
我正在尝试编写一个符合 ANSI 标准的 SQL 查询，该查询将组合每个唯一/不同的color-fruit对并对每对的单个rating值求和。因此，如果您对上表运行查询，它将产生以下结果集：
[query]
color | fruit | sum
===========================
red apple 35
blue pear 14
blue kiwi 3
green apple 12
purple kiwi 55
Thus, the query sees there are two red-applepairs in the table, and so it creates one result for the red-applepair, and adds up their constituent ratings(25 + 10 = 35), etc.
因此，查询看到表中有两个red-apple对，因此它为red-apple对创建一个结果，并将它们的组成部分ratings(25 + 10 = 35)相加，等等。
I am sure that I need to do a select for distinctcolor/fruit values, but not sure how to aggregate the ratings at the same "level/scope":
我确定我需要选择不同的颜色/水果值，但不确定如何在相同的“级别/范围”聚合评级：
SELECT
distinct(color, fruit), sum(rating)
FROM
medleys
Order doesn't matter. colorand fruitare VARCHAR(50)s and ratingis INT. Thanks in advance!
顺序无所谓。color并且fruit是 VARCHAR(50)s 并且rating是 INT。提前致谢！
回答by Andrey
SELECT color, fruit, sum(rating)
FROM medleys
GROUP BY color, fruit
Distinct is used to select distinct elements, nothing more, while you want to aggregate and for that you need GROUP BYand aggregation functions (SUM).
Distinct 用于选择不同的元素，仅此而已，而您想要聚合，为此您需要GROUP BY聚合函数 ( SUM)。
回答by Gordon Linoff
You don't need distinctat all. You need group by:
你根本不需要distinct。你需要group by：
select color, fruit, sum(rating)
from medleys
group by color, fruit
I'm answering, because I see this mistake occur. In general, you don't need select distinctat all in SQL. You can always use a group byinstead. Distinctshould be introduced after group byas a convenient short-hand.
我在回答，因为我看到这个错误发生。通常，您select distinct在 SQL 中根本不需要。您始终可以使用 agroup by代替。 Distinct应group by作为方便的简写后介绍。
回答by rbedger
SELECT `color`,`fruit`,SUM(`rating`)
FROM Medleys
GROUP BY `color`,`fruit`
回答by ljh
This should answer your question:
这应该回答你的问题：
SELECT color, fruit, sum(rating) as [sum]
FROM medleys
GROUP BY color, fruit
ORDER BY color
