Counting Rows... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Counting Rows...

Author

Topic

bobjonson4
Starting Member

2 Posts

Posted - 2010-07-20 : 09:30:48

Hi all,

I'm not sure if what I want is feasible (or sensible) the current way I am doing things, but I will quickly post anyway.

Have two tables: staff (PK staffId) and TimetableUnavailability (FK staffId).

What I want to be returned is a list of all the entries in the Staff table, alongside a count of all the records in the TimetableUnavailability table matching the StaffId in question, and meeting other criteria (which i'll show below).

Right now, have


SELECT
	Staff.StaffId,
	COUNT(TimetableUnavailability.StaffId) AS RecordCount

FROM
	Staff
	LEFT JOIN TimetableUnavailability
		ON Staff.StaffId = TimetableUnavailability.StaffId

GROUP BY
	Staff.StaffId

Which works fine.

If no records are found in TimetableUnavailability, RecordCount is set to 0 for the particular staff member.

However, if i was, for example, to set a WHERE clause, e.g.:


SELECT
	Staff.StaffId,
	COUNT(TimetableUnavailability.StaffId) AS RecordCount

FROM
	Staff
	LEFT JOIN TimetableUnavailability
		ON Staff.StaffId = TimetableUnavailability.StaffId

WHERE
	TimetableUnavailability.WeekNo = 1 AND
	TimetableUnavailability.Period BETWEEN 1 AND 4

GROUP BY
	Staff.StaffId

Staff who do not have records in TimetableUnavailability no longer show up, obviously because there TTUnavailability.WeekNo and Period are both NULL (I guess).

Is there a way I can still provide a WHERE clause, but will allow for NULLs too?

Thank you to everyone in advance for taking the time to help me out. Appreciate it. :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 09:34:35

Try without WHERE clause and put the conditions to the ON clause

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

bobjonson4
Starting Member

2 Posts

Posted - 2010-07-20 : 09:38:51

Fantastic, thank you very much for your help and the quick reply.
Got the functionality I was requiring. :)


SELECT
	Staff.StaffId,
	COUNT(TimetableUnavailability.StaffId) AS RecordCount

FROM
	Staff
	LEFT JOIN TimetableUnavailability
		ON Staff.StaffId = TimetableUnavailability.StaffId
			AND TimetableUnavailability.WeekNo = 1
			/* Etc etc */

GROUP BY
	Staff.StaffId

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 09:49:30

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources