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