SQL and Database: Count and zeros

useful info
1

I’m trying to automate an email distribution list I run monthly. Part of that is showing what countries People Managers have employees, i.e., Doe, Jane has 5 direct reports in Canada, 3 in China and 4 in India. I’ve created a COUNT subquery for each country that seems to be working:

,(SELECT Count (*) FROM Eml Eml2 LEFT JOIN Loc Loc2 ON Loc2.Loc = Eml2.Loc WHERE Eml2.Eml = 1 AND Eml2.EmlEffDt <= GetDate() AND (Eml2.EmlEfdDt IS NULL OR Eml2.EmlEfdDt >= GETDATE())AND Loc2.LocCountry = 'AU' AND Eml2.Sup = Emp.Emp) AS [Australia]
,(SELECT Count (*) FROM Eml Eml3 LEFT JOIN Loc Loc3 ON Loc3.Loc = Eml3.Loc WHERE Eml3.Eml = 1 AND Eml3.EmlEffDt <= GetDate() AND (Eml3.EmlEfdDt IS NULL OR Eml3.EmlEfdDt >= GETDATE()) AND Loc3.LocCountry = 'BZ' AND Eml3.Sup = Emp.Emp) AS [Brazil]
,(SELECT Count (*) FROM Eml Eml4 LEFT JOIN Loc Loc4 ON Loc4.Loc = Eml4.Loc WHERE Eml4.Eml = 1 AND Eml4.EmlEffDt <= GetDate() AND (Eml4.EmlEfdDt IS NULL OR Eml4.EmlEfdDt >= GETDATE()) AND Loc4.LocCountry = 'CAN' AND Eml4.Sup = Emp.Emp) AS [Canada]
,(SELECT Count (*) FROM Eml Eml5 LEFT JOIN Loc Loc5 ON Loc5.Loc = Eml5.Loc WHERE Eml5.Eml = 1 AND Eml5.EmlEffDt <= GetDate() AND (Eml5.EmlEfdDt IS NULL OR Eml5.EmlEfdDt >= GETDATE()) AND Loc5.LocCountry = 'CN' AND Eml5.Sup = Emp.Emp) AS [China]
,(SELECT Count (*) FROM Eml Eml6 LEFT JOIN Loc Loc6 ON Loc6.Loc = Eml6.Loc WHERE Eml6.Eml = 1 AND Eml6.EmlEffDt <= GetDate() AND (Eml6.EmlEfdDt IS NULL OR Eml6.EmlEfdDt >= GETDATE()) AND Loc6.LocCountry = 'DE' AND Eml6.Sup = Emp.Emp) AS [Germany]
,(SELECT Count (*) FROM Eml Eml7 LEFT JOIN Loc Loc7 ON Loc7.Loc = Eml7.Loc WHERE Eml7.Eml = 1 AND Eml7.EmlEffDt <= GetDate() AND (Eml7.EmlEfdDt IS NULL OR Eml7.EmlEfdDt >= GETDATE()) AND Loc7.LocCountry = 'GB' AND Eml7.Sup = Emp.Emp) AS [UK]
,(SELECT Count (*) FROM Eml Eml8 LEFT JOIN Loc Loc8 ON Loc8.Loc = Eml8.Loc WHERE Eml8.Eml = 1 AND Eml8.EmlEffDt <= GetDate() AND (Eml8.EmlEfdDt IS NULL OR Eml8.EmlEfdDt >= GETDATE()) AND Loc8.LocCountry = 'HK' AND Eml8.Sup = Emp.Emp) AS [Hong Kong]
,(SELECT Count (*) FROM Eml Eml9 LEFT JOIN Loc Loc9 ON Loc9.Loc = Eml9.Loc WHERE Eml9.Eml = 1 AND Eml9.EmlEffDt <= GetDate() AND (Eml9.EmlEfdDt IS NULL OR Eml9.EmlEfdDt >= GETDATE()) AND Loc9.LocCountry = 'IN' AND Eml9.Sup = Emp.Emp) AS [India]
,(SELECT Count (*) FROM Eml Eml10 LEFT JOIN Loc Loc10 ON Loc10.Loc = Eml10.Loc WHERE Eml10.Eml = 1 AND Eml10.EmlEffDt <= GetDate() AND (Eml10.EmlEfdDt IS NULL OR Eml10.EmlEfdDt >= GETDATE()) AND Loc10.LocCountry = 'SG' AND Eml10.Sup = Emp.Emp) AS [Singapore]
,(SELECT Count (*) FROM Eml Eml11 LEFT JOIN Loc Loc11 ON Loc11.Loc = Eml11.Loc WHERE Eml11.Eml = 1 AND Eml11.EmlEffDt <= GetDate() AND (Eml11.EmlEfdDt IS NULL OR Eml11.EmlEfdDt >= GETDATE()) AND Loc11.LocCountry = 'USA' AND Eml11.Sup = Emp.Emp) AS [US]

Unfortunately the other thing it does is return zeroes for any country a People Manager does not have direct reports in, which is most of them. Is there an easy way to not display the zeros? I know (I think) if they were NULL values I could use ISNULL, but It won’t work here due to the fact the value isn’t NULL.

2

Count(*) is a little different than the other things you could select since it’s a function. Try adding this to the end of each of your SELECT statements:

having count(*) > 0

EDIT: It should go inside the parentheses after all the WHERE conditions.

3

I thought about this on the way home, and I’m afraid my answer is just going to give you a different version of the same problem. I should have asked this: If you have a count of 0, what do you want displayed, nulls or nothing or something else?

4

I can’t help but think this would be a lot simpler with an OUTER JOIN and appropriate COUNT and GROUP BY.

Can you post the relevant portions of the DB schema?

5

I’m looking to display nothing, i.e., a blank “cell” if the count is zero.

6

Not sure how to do that - I’m a newbie with a smattering of knowledge. :slight_smile:

7

Yeah, that will make a difference. I suspect that Dak is correct and there’s a completely better way to do what you’re trying to do. That said, here’s what you’ll need to fix what you currently have:

,case when (SELECT Count (*) FROM Eml Eml2 LEFT JOIN Loc Loc2 ON Loc2.Loc = Eml2.Loc WHERE Eml2.Eml = 1 AND ... ) > 0 then convert(varchar(4), count(*)) else '' end AS [Australia]
,case when (SELECT Count (*) FROM Eml Eml2 LEFT JOIN Loc Loc2 ON Loc2.Loc = Eml2.Loc WHERE Eml2.Eml = 1 AND ... ) > 0 then convert(varchar(4), count(*)) else '' end AS [Brazil]
.
.
.

This is using the CASE expression to decide if the count is greater than 0. If so, it displays the count converted to a character. If not, it displays a ‘’ (nothing). I can’t think of a better way to do it right now.

EDIT: Left out a couple of “)”. It’s fixed now.

8

Assuming you’re using SQL Server Management Studio, right-click each of the tables you’re using, Script Table as…Create to…New Query Window

Then copypasta the code here.

9

Mmmmmm, copypasta.

1 Like