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

Question

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.

score 0 · Answer 1 · answered Mar 16 '16 at 19:01

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

score 0 · Answer 2 · answered Mar 16 '16 at 19:13

0

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

answered Mar 16 '16 at 19:13

Hogan

69,564
10
76
117

@bpeikes - SQL Server, DB2, Oracle that I know of -- it is part of the SQL Standard – Hogan Mar 16 '16 at 19:33
I only asked because I tried it in mysql, and it didn't work. I wasn't saying that there was any issue with it, or that your answer wasn't valid. – bpeikes Mar 16 '16 at 19:42
@bpeikes - many things in standard sql won't work in mysql. Like windowing functions while the standard is over 10 years old mysql does not include it... you get what you pay for. – Hogan Mar 17 '16 at 14:18
let's not make this a db fight. PostgreSQL supports 'values' and 'windowing' functions and is free. As for the OP, do you know if WinSQL supports it? My original question on your comment was a sincere one. I used to work with SQL Server years ago, and the 'values' function for select was not supported, so I truly wanted to know which platforms supported it. – bpeikes Mar 17 '16 at 15:01
@bpeikes -- My answer was sincere too. I get frustrated with mysql because it is different in every way from everything else. As far as I know WinSQL is just a client so it depends on what server it connects to. SQL Server has supported `values` since 2008. https://msdn.microsoft.com/en-us/library/dd776382.aspx – Hogan Mar 17 '16 at 17:58

score -1 · Answer 3 · answered Mar 16 '16 at 19:15

-1

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

answered Mar 16 '16 at 19:15

bpeikes

3,495
9
42
80

You don't need `WHERE letter IN ('A', 'B', 'C')` if you are doing a join and how is this answer different than Betsos or my answer? – Hogan Mar 16 '16 at 19:35
@Hogan, when I started writing this answer, there were no other responses. Take a look at the "answered" times. I do think both your answer and the one posted by @Bestos are fine. I wrote mine that way because I felt it made the answer clear. It also may be faster if `table` is indexed by letter. Not all optimizers will do the right thing. – bpeikes Mar 16 '16 at 19:47

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

3 Answers3