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!

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).