Get Count(*) to return zero for NULL values

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 2005 Forums

Transact-SQL (2005)

Get Count(*) to return zero for NULL values

Author

Topic

SQLexcitesMe
Starting Member

3 Posts

Posted - 2008-08-07 : 06:45:31

Hi there,

I run a report to list all incidents touched by workers. That could be created, modified or assigned to.

I've got the following SQL which handles that but a worker has to have at least one incident to appear in the report. I would like to modify it so that if a worker has no incidents they appear but are blank. I thought this would be a classic scenario for a left join but that isn't working.

Would anyone like to take a look and help?

select w.worker_full_name,
count(*) as '# Incidents'
FROM dbo.HD_workitem_current_view hd1
right join HD_worker_view w
on w.worker_nt_id = hd1.modified_by_worker_nt_id
where datediff(dd, hd1.[workitem_created_on], getdate()) <= 30
GROUP BY worker_full_name
ORDER BY '# Incidents'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 08:00:21

Does this work....


select
	w.worker_full_name
	, count(*) as [# Incidents]
FROM
	dbo.HD_workitem_current_view hd1
	right join HD_worker_view w on w.worker_nt_id = hd1.modified_by_worker_nt_id
		
where
	(
		datediff(dd, hd1.[workitem_created_on], getdate()) <= 30
		OR
		hd1.[workitem_created_on] IS NULL
		)
GROUP BY
	worker_full_name
ORDER BY
	[# Incidents]

I think the WHERE clause was eliminating the NULLS. Without sample data it's had to say for sure.

-------------
Charlie

SQLexcitesMe
Starting Member

3 Posts

Posted - 2008-08-07 : 08:26:02

Thanks but that doesn't work.

I am couting the number of incidents for each user and just want to also get the names of users without incidents. All those user names are in HD_worker_view. So I just want to list all user names in that table, then count up how many incidents each user has in the other table HD_workitem_current_view. I thought this would work just by having a left or right join but it doesn't.

I get everyone who has at least one incident but it doesn't display users with no incidents. I want it to have those users with 0 beside them for number of incidents.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-07 : 08:33:19

not convinced from you description (and no sample data etc) but, is this what you're after...


select	 w.worker_full_name
		,coalesce(SUM(Cnt),0) as [# Incidents]
FROM HD_worker_view w left join
	(	select modified_by_worker_nt_id, count(*) as Cnt
		from dbo.HD_workitem_current_view 
		where datediff(dd, [workitem_created_on], getdate()) <= 30
	) hd1
	on w.worker_nt_id = hd1.modified_by_worker_nt_id
GROUP BY worker_full_name
ORDER BY coalesce(SUM(Cnt),0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 09:09:33

Replace the COUNT(*) with COUNT(<column name>) for a column in the outer table.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources