Q

Getting a count of zero for unmatched rows

I have a reference table and a data table and wish to perform a GROUP BY. The thing I am trying to accomplish is the pull EVERY 'reference' from the reference table and count the rows from the data table, respectively. Of course I can simply perform the GROUP BY on the data table and get the stats. But this only gives me existing data counts. I was hoping to build a single query that gives counts, including those references with counts of zero. I believe this should be possible. Hopefully the question is clear.

The question is very clear. Don't feel bad, because everybody stumbles over this problem the first time.

The situation can be illustrated with the following sample data:

References
ID Reference
1  HTML  
2  CSS 
3  PHP
4  SQL

       
Data
ID RefID 
24  1
24  2
24  4
27  2
27  4
31  1
31  4

In order to understand how to get the correct counts, let's first write a simple LEFT OUTER JOIN and show the results:

select References.ID
     , References.Reference 
     , Data.ID     as Data_ID
  from References
left outer
  join Data
    on References.ID = Data.RefID
order
    by References.ID
     , Data.ID      

       
ID Reference Data_ID      
1  HTML      24 
1  HTML      31 
2  CSS       24
2  CSS       27
3  PHP       null
4  SQL       24      
4  SQL       27      
4  SQL       31 

Notice the row in the result set for PHP. This row shows up in a LEFT OUTER JOIN, but would be eliminated in an INNER JOIN. (Make sure you understand why.)

Now let's add the GROUP BY and the COUNT() function.

select References.ID
     , References.Reference 
     , count(*)     as Data_rows
  from References
left outer
  join Data
    on References.ID = Data.RefID
group
    by References.ID
     , Data.ID      
order
    by References.ID

       
ID Reference Data_rows      
1  HTML      2
2  CSS       2
3  PHP       1
4  SQL       3 

Wait a minute, that's wrong! We know there are no rows for PHP, but the count is showing 1. Why is this?

The answer is: COUNT(*) counts rows. There is a row for PHP in the result set.

What you have to do is count the number of Data.RefID values. Note that in the result set, the value of this column for the PHP row is NULL. And as you know, aggregate functions like COUNT() and SUM() ignore nulls.

select References.ID
     , References.Reference 
     , count(Data.RefID)     as Data_rows
  from References
left outer
  join Data
    on References.ID = Data.RefID
group
    by References.ID
     , Data.ID      
order
    by References.ID
     
ID Reference Data_rows      
1  HTML      2
2  CSS       2
3  PHP       0
4  SQL       3 

See the difference?

This was first published in May 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close