CODEWITHSUNDEEP
sqlpostgresql
Yannick
Yannick

Reputation: 3663

Building SQL with GROUP BY

I need to create a SQL that will give me the total number of fans for a category.

select count(fo.id) 
from fans_fanofvote as fov, fans_fanof as fo
where fov.fanof_id = fo.id
GROUP BY fo.fanofcategory_id

But the problem is that a fan can vote more then once for a category and because of that my query returns 2 fans for MMA but it should be 1 since the FAN (id:1) has voted twice for that category. 

FanOf
ID FANOFCATEGORY  NAME  
== ============== =====
1   1             Ronda Rousey
2   1             GSP
3   2             Carey Price
4   2             Sidney Crosby

FanOfCategory

ID  NAME
== ======
1   MMA
2   Hockey
3   Football

FanOfVote

ID  FAN FANOF
== ==== =====
1   1   1
2   1   2
3   2   3
4   1   4
5   1   3

Upvotes: 0

Views: 31

Answers (1)

Stan Shaw
Stan Shaw

Reputation: 3034

To get each category and the number of distinct fans that voted for it:

select foc.name, count(distinct fov.fan) 
from fans_fanofvote as fov
join fans_fanof as fo
    on fov.fan = fo.id
join fanofcategory foc
    on fo.fanofcategory = foc.id
GROUP BY foc.name

To get the number of distinct fans for a specific category:

declare @CategoryID int = 1 //This should be a parameter, but just putting it here so you can test

select count(distinct fan)
from fanofvote
where fanof = @CategoryID

Upvotes: 2

Related Questions