CODEWITHSUNDEEP
sqlwinsql
jamesvphan
jamesvphan

Reputation: 1985

SQL: Return a count of 0 with count(*)

I am using WinSQL to run a query on a table to count the number of occurrences of literal strings. When trying to do a count on a specific set of strings, I still want to see if some values return a count of 0. For example:

select letter, count(*)
from table
where letter in ('A', 'B', 'C')
group by letter

Let's say we know that 'A' occurs 3 times, 'B' occurs 0 times, and 'C' occurs 5 times. I expect to have a table returned as such:

letter count
A        3
B        0
C        5

However, the table never returns a row with a 0 count, which results like so: letter count A 3 C 5

I've looked around and saw some articles mentioning the use of joins, but I've had no luck in correctly returning a table that looks like the first example.

Upvotes: 0

Views: 692

Answers (3)

bpeikes
bpeikes

Reputation: 3715

I'm not that familiar with WinSQL, but it's not pretty if you don't have the values that you want in the left most column in a table somewhere. If you did, you could use a left join and a conditional. Without it, you can do something like this:

SELECT all_letters.letter, IFNULL(letter_count.letter_count, 0)
FROM 
   (
    SELECT 'A' AS letter 
    UNION 
    SELECT 'B' AS letter 
    UNION
    SELECT 'C' AS letter
   ) all_letters 

   LEFT JOIN 

   (SELECT letter, count(*) AS letter_count 
    FROM table 
    WHERE letter IN ('A', 'B', 'C') 
    GROUP BY letter) letter_count 

   ON all_letters.letter = letter_count.letter

Upvotes: -1

Hogan
Hogan

Reputation: 70538

on many platforms you can now use the values statement instead of union all to create your "in line" table - like this

select t.letter, count(mytable.letter) 
from ( values ('A'),('B'),('C') ) as t(letter)
left join mytable on t.letter = mytable.letter    
group by t.letter

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can create an in-line table containing all letters that you look for, then LEFT JOIN your table to it:

select t1.col, count(t2.letter) 
from (
   select 'A' AS col union all select 'B' union all select 'C'
) as t1
left join table as t2 on t1.col = t2.letter    
group by t1.col

Upvotes: 0

Related Questions