Q:

Using DISTINCT and COUNT together

I have two different product_id with multiple keywords per each.
Following statement is not working:
SELECT distinct product_id, COUNT(distinct keyword) as keyword_sum FROM data;

Currently result looks like this:
product_id     keyword_sum
2              47

Desired result would look like this:
product_id     keyword_sum
2              26
3              21

Any help much appreciated!

A:

Group by the column you want to be unique and then you can use aggregate functions like counton each element of the group.
SELECT product_id, COUNT(distinct keyword) as keyword_sum 
FROM data
GROUP BY product_id

