UNION showing zeros when COUNT(*)==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

General SQL Server Forums

Database Design and Application Architecture

UNION showing zeros when COUNT(*)==0

Author

Topic

Tokajac
Starting Member

5 Posts

Posted - 2011-12-10 : 06:30:19

Hello,


SELECT count(*), months.m_disp FROM people p, (
SELECT '01' as m, 'Jan' as m_disp
UNION
SELECT '02' as m, 'Feb' as m_disp
UNION
SELECT '03' as m, 'Mar' as m_disp
UNION 
SELECT '04' as m, 'Apr' as m_disp
UNION 
SELECT '05' as m, 'May' as m_disp
UNION 
SELECT '06' as m, 'Jun' as m_disp
UNION 
SELECT '07' as m, 'Jul' as m_disp
UNION 
SELECT '08' as m, 'Aug' as m_disp
UNION 
SELECT '09' as m, 'Sep' as m_disp
UNION 
SELECT '10' as m, 'Oct' as m_disp
UNION 
SELECT '11' as m, 'Nov' as m_disp
UNION 
SELECT '12' as m, 'Dec' as m_disp
) months
GROUP BY months.m

where clause determines which people to select;

All works well but months where there are no people maching criteria don't appear.

So now I get:


4,Jan
19,Mar
2,Jul
6,Aug
7,Oct
85,Nov
92,Dec

and I'm working on getting:


4,Jan
0,Feb
19,Mar
0,Apr
0,Maj
0,Jun
2,Jul
6,Aug
0,Sep
7,Oct
85,Nov
92,Dec

Share ideas, please

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-10 : 06:52:53

is that the full query that you have posted ?

there a where clause that you didn't show it ?

KH
[spoiler]Time is always against us[/spoiler]

Tokajac
Starting Member

5 Posts

Posted - 2011-12-10 : 08:04:19

Thank you for your reply, khtan!

Simplest case:
WHERE MONTH(people.birth_dt)=months.m
DB is MySQL: I can create .sql for test if you would look into it

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources