Multiple COUNT in sql issue

Question

I have a little wired issue.
I have to select two count from query Likes and Collects but when I add second query instead of 2 likes and 10 collects I get 10 likes and 10 collects.
What am I doing wrong here?

select  COUNT(tl.ItemLikeId) as a, COUNT(tib.PacketId) as b
from Items i
left join ItemLikes il
on il.ItemId = i.ItemId
left join ItemsInPackets iip
on iip.ItemId = i.ItemId
where i.ItemId = 14591

zimdanen · Accepted Answer · 2012-11-30 19:41:30Z

6

Try SELECT COUNT(DISTINCT tl.ItemLikeId) AS a, COUNT(DISTINCT tib.PacketId) as b.

Your join gives you ten rows, so you have ten IDs from each table. However, not all of the IDs are unique. You're looking for unique IDs.

answered Nov 30, 2012 at 19:41

zimdanen

5,6367 gold badges47 silver badges96 bronze badges

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

1 Comment

1110 Over a year ago

Yes I see my error now. in ItemInPocket is N:N table. So I didn't add auto increment column. When I use distinct on those keys it works :)

cadrell0 · Accepted Answer · 2012-11-30 19:43:51Z

2

Count returns the number of rows. Not the number of rows with a value, and not the number of distinct rows.

To get number row rows with a value

select  SUM(CASE WHEN tl.ItemLikeId IS NOT NULL THEN 1 ELSE 0 END) as a, 
        SUM(CASE WHEN tib.PacketId IS NOT NULL THEN 1 ELSE 0 END) as b

To get the number of distinct values, do what zimdanen suggested and use COUNT(DISTINCT)

select  COUNT(DISTINCT tl.ItemLikeId) as a, COUNT(DISTINCT tib.PacketId) as b

Another approach, if all you are using ItemLikes and ItemsInPackets for are the counts

select
    (
        SELECT COUNT(ItemLikeId) 
        FROM ItemLikes
        WHERE ItemId = i.ItemId
    ) as a,
    (
        SELECT COUNT(PacketId) 
        FROM ItemsInPackets
        WHERE ItemId = i.ItemId
    ) as b
from Items i
where i.ItemId = 14591

answered Nov 30, 2012 at 19:43

cadrell0

17.4k5 gold badges55 silver badges70 bronze badges

2 Comments

zimdanen Over a year ago

Actually, COUNT(rowname) will give you a count of rows that have a value for rowname. COUNT(1) or COUNT(*) will give you a straight count of rows.

cadrell0 Over a year ago

@zimdanen Good call. I was going on memory, but I checked and you are correct.

Collectives™ on Stack Overflow

Multiple COUNT in sql issue

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related