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 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.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading