CODEWITHSUNDEEP
sqlsql-servert-sql
1110
1110

Reputation: 6829

Multiple COUNT in sql issue

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

Upvotes: 2

Views: 98

Answers (2)

cadrell0
cadrell0

Reputation: 17307

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

Upvotes: 2

zimdanen
zimdanen

Reputation: 5626

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.

Upvotes: 6

Related Questions