Help With SQL Query - 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

Help With SQL Query

Author

Topic

markcr
Starting Member

1 Post

Posted - 2012-09-26 : 12:58:49

Hi. I have a staff table and an assignments table. A staff member can have many assignments. I want to query the staff members based on their number of assignments for the month. The staff members should be ordered by the number of assignments from least to greatest. Here's what I'm starting with:

SELECT staff.staff_id, staff.first_name + ' ' + staff.last_name, COUNT( ...) this is where I'm not sure what to do? WHERE staff.staff_id = assignments.staff_id ORDER BY COUNT( ...again not sure...)

Thanks for any help you can provide. :-)

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-26 : 13:16:23

PLease show some sample of data to show us what your needs are.

the structure of the table will help too.

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 13:20:11

Something like this?

SELECT
	s.staff_id,
	s.first_name + ' ' + s.last_name AS staff_name,
	COUNT(DISTINCT a.assignment_id) AS Assignments
FROM
	staff s
	LEFT JOIN assignments a ON
		a.staff_id = s.staff_id
GROUP BY
	s.staff_id,
	s.first_name + ' ' + s.last_name
ORDER BY
	Assignments;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources