SQL Query - Count non existing records as 0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query - Count non existing records as 0

Author

Topic

perels
Starting Member

23 Posts

Posted - 2003-05-20 : 09:34:47


Hi, 

In my application there can at maximum be 6 countries (DK,FO,GL,IS,NO,SE), with an unlimited number of groups in each country.

My table (Groups) looks like this

ID LCID CompanyID Name
-- ---- --------- ----
1  DK   1         Groupbla 
2  FO   1         Groupblah
3  DK   1         Groupblabla
4  DK   1         123group
5  SE   1         testgroup
6  SE   2	  group2
7  DK   2         somegroup
8  NO   3         test


I'm running this Query

SELECT LCID, COUNT(*) AS [Count] FROM Groups WHERE CompanyID = 1 
GROUP BY LCID ORDER BY LCID ASC


And the output will be:

LCID Count
---- -----
DK   3
FO   1
UK   1

this is fine. But what I want to do, is the following


LCID Count
---- -----
DK   3
FO   1
GL   0
IS   0
NO   0
SE   1

that is, return 0 for all those countries that have no groups in them.
I want to always return 6 records (DK,FO,GL,IS,NO,SE), no mather if the country does not exists in the Groups-table.

Does anybody have some SQL for my problem - or maybe an alternative solution? Or do you need more clarification???

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 09:41:39

First off, you need a table somewhere of all possible countries. I hope you have this table in your database!

Then, you just need a LEFT OUTER JOIN.

SELECT A.LCID, ISNULL([Count],0) as Count
FROM
Countries A
LEFT OUTER JOIN
(
SELECT LCID, COUNT(*) AS [Count] FROM Groups WHERE CompanyID = 1
GROUP BY LCID ORDER BY LCID ASC
)
B
ON
A.LCID = B.LCID

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-20 : 09:51:21

...or

SELECT Locales.LCID, COUNT(*) FROM
Locales LEFT JOIN Groups
ON Locales.LCID = Groups.LCID
AND Groups.CompanyID = 1
ORDER BY Locales.LCID

You do have a "Locales" table, dont you? If not, I suggest you create one and fill it with all the possible LCIDs you can have.

OS

perels
Starting Member

23 Posts

Posted - 2003-05-20 : 09:53:02

I had to add the TOP 6 in your query:

 
SELECT  A.LCID, ISNULL([Count],0) as Count
FROM 
Countries A 
LEFT OUTER JOIN (SELECT TOP 6 LCID, COUNT(*) AS [Count] FROM Groups WHERE FK_CID = 1 
GROUP BY LCID ORDER BY LCID ASC)
B 
ON 
A.LCID = B.LCID

Appearantly SQL Server complains when using LEFT OUTER JOIN's with out the TOP-keyword:


The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

But thank you Jeff for a quick answer

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 09:55:49

oops... didn't notice the order by. take that out . ordering is not allowed in SUBQUERIES, since it makes no sense to order at that point -- do the ORDER BY at the last step.

SELECT A.LCID, ISNULL([Count],0) as Count
FROM
Countries A
LEFT OUTER JOIN
(
SELECT LCID, COUNT(*) AS [Count] FROM Groups WHERE CompanyID = 1
GROUP BY LCID ~~ORDER BY LCID ASC~~
)
B
ON
A.LCID = B.LCID
ORDER BY A.LCID

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources