Counts across mulitple groupings

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

SQL Server Development (2000)

Counts across mulitple groupings

Author

Topic

Skydolphin
Starting Member

43 Posts

Posted - 2006-12-11 : 13:55:03

Hi all,

I need to build a query that will return counts on several groupings. We have an employee table that contains not only the employee name
but also their location (Countrycode) and department (departmentdode). Based on a month ending date, I need to find out how many employees we have for each
country broken out by department. The end result should look something like this.

Month Ending: 11/30/2006
CountryCode 30 20 40 50 60 Total
Department Sales 30 3 3 2 18 56
Professional Services 5 10 4 3 9 31
Marketing 4 4 0 0 2 10
Product Development 3 56 0 0 0 59
Operations- Support 5 2 0 0 5 12
General & Administrative 9 8 2 0 4 23

I am not a SQL expert and I'm having some difficulty figuring this one out.
Can someone please point me in the right direction?

Thanks,

Rhonda

Rhonda

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 14:08:18

SELECT DepartmentCode,
SUM(CASE WHEN CountryCode = 30 THEN 1 ELSE 0 END) AS '30',
SUM(CASE WHEN CountryCode = 20 THEN 1 ELSE 0 END) AS '20',
SUM(CASE WHEN CountryCode = 40 THEN 1 ELSE 0 END) AS '40',
SUM(CASE WHEN CountryCode = 50 THEN 1 ELSE 0 END) AS '50',
SUM(CASE WHEN CountryCode = 60 THEN 1 ELSE 0 END) AS '60',
SUM(1) AS 'Total'
FROM YourTableNameHere
WHERE SomeDateTimeColumnHere >= '20061201' AND SomeDateTimeColumnHere < '20070101'
GROUP BY DepartmentCode

Peter Larsson
Helsingborg, Sweden

EDIT: Changed CountryCode = X

Skydolphin
Starting Member

43 Posts

Posted - 2006-12-11 : 14:13:42

Thanks Peter,

I was so close earlier and got frustrated and gave up. This is something I can work with.

Rhonda

Rhonda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources