Ask the Expert

How to return a zero in SQL instead of no row back for a select count

I'm doing a select count(*) grouped by Company Code and State where Resident='N'. I get a row back for all Company/State combos where there is at least one Resident='N', but if there is no entry for Resident='N' I get no row back at all. I'd like to get a zero back instead of nothing for such combinations. How can I go about this?

    Requires Free Membership to View

To get a return of zero in SQL instead of getting no returns in some instances, there are two steps to follow:

First, you can move the condition from the WHERE clause into the SELECT clause as a conditional count:

SELECT CompanyCode
     , State
     , SUM(CASE WHEN Resident = 'N'
                THEN 1 ELSE 0 END) AS non_residents
  FROM datatable
GROUP
    BY CompanyCode
     , State

This will not, of course, include any Company/State combinations which have no rows whatsoever. That's the second part of the answer. For this, you will have to cross join the Company and State tables—assuming you have these—and then LEFT OUTER JOIN the result to the datatable.

This was first published in September 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: