Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding GROUP BY to PIVOT operator (how?) Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 8:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
I am trying to show how many employees were hired in each department by startyear (2001, 2002, 2003) and then to rollup these year totals by department. So, for example, Engineering hired one employee in 2001, another in 2002, and another in 2003 with 3 total in the Engineering department.
I am able to get the first part of this problem solved with a PIVOT (showing 1 employee under each year) but I'm unable to add a GROUP BY clause after the PIVOT that will rollup each years total by department to show total number (3) of employees hired by department (eg. Enginneering).
Is GROUP BY the solution or is there another way to roll up by DeptName?
---this works:
select DeptName, [2001], [2002], [2003]
from
(select DeptName, EmpID, year(StartDate) as startyear
from StaffHistory
) as p
PIVOT (count(empid) for Startyear in ([2001], [2002], [2003])) as pvt

---this doesn't
select DeptName, [2001], [2002], [2003]
from
(select DeptName, EmpID, year(StartDate) as startyear
from StaffHistory
) as p
PIVOT (count(empid) for Startyear in ([2001], [2002], [2003])) as pvt
GROUP BY DeptName

CREATE TABLE StaffHistory 
(DeptName varchar(40)NOT NULL, EmpID int NOT NULL, StartDate datetime NOT NULL, EndDate datetime NULL);
INSERT INTO StaffHistory
VALUES
('Engineering', 1, '2003-12-16 00:00:00.000', null),
('Tool Design', 2, '2003-07-01 00:00:00.000', null),
('Sales', 3, '2003-11-01 00:00:00.000', null),
('Engineering', 4, '2002-07-01 00:00:00.000', null),
('Tool Design', 5, '2002-07-01 00:00:00.000', null),
('Sales', 6, '2002-11-01 00:00:00.000', null),
('Engineering', 7, '2001-07-01 00:00:00.000', null),
('Tool Design', 8, '2001-07-01 00:00:00.000', null),
('Sales', 9, '2001-11-01 00:00:00.000', null);
Post #1357098
Posted Monday, September 10, 2012 9:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:49 AM
Points: 43, Visits: 317
Try this, it will give you a start.

SELECT DeptName
, [2001]
, [2002]
, [2003]
, [All]
FROM (
SELECT DeptName
, startyear = isnull(cast(year(StartDate) AS CHAR(4)), 'All')
, Cnt = Count(empid)
FROM StaffHistory
GROUP BY DeptName
, year(StartDate)
WITH rollup
) src
pivot(sum(Cnt) FOR startyear IN (
[2001]
, [2002]
, [2003]
, [All]
)) pvt
WHERE DeptName IS NOT NULL



~Sam.

http://svangasql.wordpress.com
Post #1357117
Posted Monday, September 10, 2012 9:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
This solution works, Thanks Sam.
I have a question about this line:
, startyear = isnull(cast(year(StartDate) AS CHAR(4)), 'All')
I understand ISNULL works because a NULL for startdate is created by the creation of an All column. But, can you explain why it is that you have to use the CAST function, as in , "cast(year(StartDate) AS CHAR(4)"? Is it because datetime is not convertible to a string value?
Post #1357121
Posted Tuesday, September 11, 2012 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:49 AM
Points: 43, Visits: 317
Look at the remarks section here: http://msdn.microsoft.com/en-us/library/ms184325.aspx

When using ISNULL, check and replacement expressions should be of compatible data types.


~Sam.

http://svangasql.wordpress.com
Post #1357469
Posted Tuesday, September 11, 2012 11:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
For general knowledge and maybe performance (you'll have to test your case), I leave you with my personal preference:
SELECT DeptName,
SUM( CASE WHEN YEAR( StartDate) = 2001 THEN 1 ELSE 0 END) [2001],
SUM( CASE WHEN YEAR( StartDate) = 2002 THEN 1 ELSE 0 END) [2002],
SUM( CASE WHEN YEAR( StartDate) = 2003 THEN 1 ELSE 0 END) [2003],
COUNT(*) [All]
FROM StaffHistory
GROUP BY DeptName

You can find more information here:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1357615
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse