# SQL and Database: Count and zeros

**URL:** <https://cog.discourse.group/t/sql-and-database-count-and-zeros/767>\
**Category:** useful info\
**Created:** [June 2, 2014, 5:12pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767 "2014-06-02T17:12:30Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![TechnoMistress](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/technomistress/32/3981_2.png) [@TechnoMistress](https://cog.discourse.group/u/TechnoMistress)\
**Post date:** [June 2, 2014, 5:12pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/1 "2014-06-02T17:12:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Brazil](https://avatars.discourse-cdn.com/v4/letter/b/b9bd4f/32.png) [@Brazil](https://cog.discourse.group/u/Brazil)\
**Post date:** [June 2, 2014, 9:21pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/2 "2014-06-02T21:21:00Z")

</div>

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:

```auto
having count(*) > 0

```

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

---

<div class="post-metadata">

**Author:** ![Brazil](https://avatars.discourse-cdn.com/v4/letter/b/b9bd4f/32.png) [@Brazil](https://cog.discourse.group/u/Brazil)\
**Post date:** [June 2, 2014, 11:28pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/3 "2014-06-02T23:28:04Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![dakboy](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/dakboy/32/68_2.png) [@dakboy](https://cog.discourse.group/u/dakboy)\
**Post date:** [June 3, 2014, 1:16pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/4 "2014-06-03T13:16:21Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![TechnoMistress](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/technomistress/32/3981_2.png) [@TechnoMistress](https://cog.discourse.group/u/TechnoMistress)\
**Post date:** [June 3, 2014, 2:28pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/5 "2014-06-03T14:28:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TechnoMistress](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/technomistress/32/3981_2.png) [@TechnoMistress](https://cog.discourse.group/u/TechnoMistress)\
**Post date:** [June 3, 2014, 2:29pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/6 "2014-06-03T14:29:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Brazil](https://avatars.discourse-cdn.com/v4/letter/b/b9bd4f/32.png) [@Brazil](https://cog.discourse.group/u/Brazil)\
**Post date:** [June 3, 2014, 3:02pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/7 "2014-06-03T15:02:49Z")

</div>

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:

```auto
,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.

---

<div class="post-metadata">

**Author:** ![dakboy](https://sea1.discourse-cdn.com/flex001/user_avatar/cog.discourse.group/dakboy/32/68_2.png) [@dakboy](https://cog.discourse.group/u/dakboy)\
**Post date:** [June 3, 2014, 5:21pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/8 "2014-06-03T17:21:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Brazil](https://avatars.discourse-cdn.com/v4/letter/b/b9bd4f/32.png) [@Brazil](https://cog.discourse.group/u/Brazil)\
**Post date:** [June 3, 2014, 5:38pm UTC](https://cog.discourse.group/t/sql-and-database-count-and-zeros/767/9 "2014-06-03T17:38:41Z")

</div>

Mmmmmm, copypasta.