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

Want your row to return a zero instead of getting no row backs for some combinations when doing a select count in SQL? Find out how here.

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?

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
    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.

Dig Deeper on Oracle development languages

Data Management
Business Analytics
  • The 3 daily Scrum questions

    The 2020 Scrum Guide removed all references to the three daily Scrum questions, but does that mean you shouldn't ask them anymore?

  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

Data Center
Content Management