SQL query with distinct and sum

Question

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

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

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

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

Ericson

Answer 1

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 BY and aggregation functions (SUM).

Godliman

Answer 2

You don't need distinct at all. You need 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 distinct at all in SQL. You can always use a group by instead. Distinct should be introduced after group by as a convenient short-hand.

Madelenemadelin

And what if you wanted to know all the unique colors of fruits, or maybe all the unique fruits you have in your table? Would you use DISTINCT then? – Nonfulfillment

You could, but the point is that you could also replace it with GROUP BY - aggregation is the overall concept that needs to be understood. – Slather

Answer 3

SELECT `color`,`fruit`,SUM(`rating`)
FROM Medleys
GROUP BY `color`,`fruit`

SQL Fiddle Example

Nonfulfillment

Answer 4

This should answer your question:

SELECT color, fruit, sum(rating) as [sum]
FROM medleys
GROUP BY color, fruit
ORDER BY color

Troublemaker