Count how many gifts of each type were sent-CodePudding

I have a gifts table

id | name | price

and a giftings table to record everything that is sent

id | gift | from | to

with giftings.gift being a FK to gifts.id and from and to being FK to a users table

I want to count how many of each gift a user has received, so an output would be like this

star: 5
diamond: 20
heart: 0

At the moment, I think its working with this query but It wont show a gift if the count is 0.

select gifts.id, gifts.name, count(giftings.gift) from gifts join giftings on gifts.id = giftings.gift where "to" = 5839 group by gifts.id, gifts.name

I have tried different joins, and I know I could do this with a sub query but im trying to do it in a single query because I know its possible.

Many thanks

CodePudding user response：

You need a LEFT join and the condition for the user in the ON clause:

SELECT g.id, g.name, 
       COUNT(s.gift) 
FROM gifts g LEFT JOIN giftings s  
ON g.id = s.gift AND s."to" = 5839 
GROUP BY g.id, g.name;