CODEWITHSUNDEEP
sqlsql-server
Livewire
Livewire

Reputation: 77

COUNT Nulls in GROUP BY in same result (SQL Server)

I'm a little confused about how to count Null values in a query.

I have a basic table which has the number of phone calls for each category. I currently count how many times a call has been received about a category like this:

SELECT COUNT (Calls) AS Calls, Category
FROM dbo.Calls
GROUP BY Category

This works fine and gives me an output showing the total numbers of calls for each category. However, some categories have had no calls. These are not displayed because COUNT does not count Null values.

With this in mind, how can I get one single output that both counts all the calls to each category and show 0 next to each category which doesnt have any calls?

Some examples on the internet show that this is possible when two tables are involved and doing an OUTER JOIN. But if I just have the one table - what can I do here?

Upvotes: 1

Views: 2537

Answers (2)

canon
canon

Reputation: 41675

Counts for categories which aren't represented in dbo.Calls

To accomplish this, you'll need a list of the categories which must always appear in the results... and then you'll need to perform a left join between those categories and your calls.

Do you have a categories table? If so:

select
 cat.Category
,count(c.Category)
from dbo.Categories as cat
    left join dbo.Calls as c
    on c.Category = cat.Category
group by cat.Category

Here's a SQL Fiddle

If not, you could explicitly list the category values (assuming they're strings):

select
 cat.Category 
,count(c.Category) as Calls
from (values
   ('Bat-Phone')      
  ,('Domestic')
  ,('International')
) cat(Category)
  left join dbo.Calls as c
  on c.Category = cat.Category 
group by cat.Category

Here's another SQL Fiddle

Counts for uncategorized calls

If you simply want to see uncategorized calls as well, use coalesce() or isnull() to provide a dummy category for nulls, like so:

select
 isnull(c.Category, 'Uncategorized') as Category
,count(*)
from Calls as c
group by isnull(c.Category, 'Uncategorized')

And another SQL Fiddle

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31785

Wouldn't this do it, or am I missing something?

SELECT SUM(CASE WHEN Calls IS NOT NULL THEN 1 ELSE 0 END) AS Calls, Category
FROM dbo.Calls
GROUP BY Category

Upvotes: 2

Related Questions