SQL query with distinct and sum
ansi-sql, distinct, sql, sum
Solution
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`).
Problem
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!