Listing zero count - 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

Listing zero count

Author

Topic

nigelc
Starting Member

20 Posts

Posted - 2007-01-22 : 06:36:15

I have a query that lists the names of all employees and the number of training modules they have sat. The query is below:

SELECT distinct pps_principals.name AS principals_name,
COUNT(*) AS coursecount
FROM (PPS_SCOS JOIN PPS_TRANSCRIPTS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID)
JOIN PPS_PRINCIPALS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
and pps_scos.name like 'MT%'
and pps_principals.login like '%testlogin%'
and pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
GROUP BY pps_principals.name
ORDER BY coursecount desc

The cont goes all the way down to those who have sat 1 module.

I now however need to be able to report all those names of individuals who have sat 0 courses.

Any help appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 07:04:10

Write

HAVING COUNT(*) = 0

after the GROUP BY line and before ORDER BY line.

Peter Larsson
Helsingborg, Sweden

nigelc
Starting Member

20 Posts

Posted - 2007-01-22 : 08:52:57

Peter,

Thanks. I have added the code where you suggested and this works perfect for any number but 0. It should return several hundred rows but just returns blank.

Thanks

Nigel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 08:59:52

This?

SELECT		pps_principals.name AS principals_name, 
		COUNT(PPS_SCOS.SCO_ID) AS coursecount 
FROM		PPS_PRINCIPALS
LEFT JOIN	PPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
			AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
			AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
			AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOIN	PPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
			AND pps_scos.name like 'MT%'
WHERE		pps_principals.login like '%testlogin%'
GROUP BY	pps_principals.name
--HAVING		COUNT(PPS_SCOS.SCO_ID) = 0
ORDER BY	2 desc

Peter Larsson
Helsingborg, Sweden

nigelc
Starting Member

20 Posts

Posted - 2007-01-22 : 09:16:40

Peter,

Excellent this works perfectly.

Thank you very much.

Regards,

Nigel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources