Building SQL with GROUP BY

Question

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

Still the same result.

Yannick
– Yannick

2015-12-18 20:10:40 +00:00
Commented Dec 18, 2015 at 20:10 — Yannick
– Yannick, Commented Dec 18, 2015 at 20:10
count(distinct fov.fan)

shawnt00
– shawnt00

2015-12-18 20:13:04 +00:00
Commented Dec 18, 2015 at 20:13 — shawnt00
– shawnt00, Commented Dec 18, 2015 at 20:13

Stan Shaw · Accepted Answer · 2015-12-18 20:38:29Z

2

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

edited Dec 18, 2015 at 20:38

answered Dec 18, 2015 at 20:12

Stan Shaw

3,0541 gold badge14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stan Shaw Over a year ago

@Yannick - let me know how this works for you and I'll try to update my response.

shawnt00 Over a year ago

You're counting the wrong column. fo.id is unique per category and the count should always return 1.

Collectives™ on Stack Overflow

Building SQL with GROUP BY

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related