SQL查询与不同和 [英] SQL query with distinct and sum

问题描述

我有下面的medleys表，其中结合了colors，fruits和ratings:

I have the following medleys table that combines colors, fruits and 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

我正在尝试编写一个符合ANSI的SQL查询，该查询将结合每个唯一/不同的color-fruit对，并对每个对的各个rating值求和.因此，如果您在上面的表上运行查询，它将产生以下结果集:

I am trying to write an ANSI-compliant SQL query that will combine every unique/distinct color-fruit pair and sum each pair's individual rating values. Thus if you ran the query on the table above it would produce the following result sets:

[query]
color   |   fruit   |   sum
===========================
red         apple       35
blue        pear        14
blue        kiwi        3
green       apple       12
purple      kiwi        55

因此，该查询看到表中有两个red-apple对，因此它为red-apple对创建一个结果，并将其组成ratings相加(25 + 10 = 35)，等等.

Thus, the query sees there are two red-apple pairs in the table, and so it creates one result for the red-apple pair, and adds up their constituent ratings (25 + 10 = 35), etc.

我确定需要对与众不同的颜色/水果值进行选择，但不确定如何在相同的级别/范围"下汇总评分:

I am sure that I need to do a select for distinct color/fruit values, but not sure how to aggregate the ratings at the same "level/scope":

SELECT
    distinct(color, fruit), sum(rating)
FROM
    medleys

顺序无关紧要. color和fruit是VARCHAR(50)s，rating是INT.预先感谢！

Order doesn't matter. color and fruit are VARCHAR(50)s and rating is INT. Thanks in advance!

推荐答案

SELECT color, fruit, sum(rating)
FROM medleys
GROUP BY color, fruit

Distinct用于选择不同的元素，仅在要聚合时才需要，为此需要GROUP BY和聚合函数(SUM).

Distinct is used to select distinct elements, nothing more, while you want to aggregate and for that you need GROUP BY and aggregation functions (SUM).

这篇关于SQL查询与不同和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！