Display Null record as Zero - SQL Server Forums

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

New to SQL Server Programming

Display Null record as Zero

Author

Topic

bhaasjoshi
Starting Member

17 Posts

Posted - 2009-10-14 : 03:26:59

Hi Friends,

I have the following table.

Company Employee Dept
C 001 A
C 002 B
C 003 A

My Query is SELECT Company,count(Employee) FROM table where Dept=C GROUP BY Company.

Please note that Since Dept=C doesnot exist in the table the count will be Null. I would like the output to be something like this.

Company Count
C 0

Best Regards,
Bhaas

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-14 : 04:19:58

Select Company, Case when employee is null then '0' else employee end FROM table etc etc etc...

You see?

That will make all null returns of employee come back as '0'
Is there something else you wanted?

[/fail at query]

boreddy
Posting Yak Master

172 Posts

Posted - 2009-10-14 : 05:30:36

when there is no count
then count will null be,it displays the 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-14 : 08:17:49

[code]
If exists(SELECT * FROM table where Dept=C)
SELECT Company,count(Employee) FROM table where Dept='C' GROUP BY Company
else
select 'C' as company,0 as [count]
[/code]

Madhivanan

Failing to plan is Planning to fail

sumitbatra1981
Starting Member

17 Posts

Posted - 2009-10-14 : 09:02:02

SELECT Company,isnull(count(Employee),0) FROM table where Dept=C GROUP BY Company

Regards,
Sumit Batra
Software Engineer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-14 : 09:13:12

quote:
Originally posted by sumitbatra1981

SELECT Company,isnull(count(Employee),0) FROM table where Dept=C GROUP BY Company

Regards,
Sumit Batra
Software Engineer

This wont wont work if there are no data for Dept='C'

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-14 : 09:16:30

Hey Mr. Software Engineer,
there is no record in the table with Dept=C.
Dept=C (without quotes) wouldn't work.

Because there is no record the only solution is given by madhi.

No, you're never too old to Yak'n'Roll if you're too young to die.

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-14 : 09:18:46

Yeh Mr. Software Engineer. No-one attempts to own Madhivanan! Madhivanan is the Owner, not the owned.

[/fail at query]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources