Adding row total and column to Pivot

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

Transact-SQL (2008)

Adding row total and column to Pivot

Author

Topic

raghu_grdr
Starting Member

17 Posts

Posted - 2011-12-07 : 23:39:18

Hi i have below query.

SELECT *
FROM(
SELECT
YEAR(calldate) [Year],
--COUNT(REQUESTS.reqid),
CASE MONTH(calldate)
-- WHEN 1 THEN 'January'
-- WHEN 2 THEN 'February'
-- WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
--WHEN 12 THEN 'December'
END as [Month],
--customerid,
-- SCHEDULE_EMPLOYEE.EMPID 'EMPID',
EMPLOYEE.EMPLOYEENAME 'EMPLOYEENAME',
REQUESTS.reqid

FROM requests
INNER JOIN SCHEDULE_EMPLOYEE ON SCHEDULE_EMPLOYEE.REQID = REQUESTS.REQID
INNER JOIN EMPLOYEE ON SCHEDULE_EMPLOYEE.EMPID = EMPLOYEE.EMPID
where YEAR(calldate) = 2011
) yearwise
--WorkOrders
PIVOT
(
count(reqid)
FOR [Month] IN (
--[January],[February],[March],
[April],
[May],[June],[July],[August],
[September],[October],[November]
-- ,[December]
)
) AS PivotTable

ORDER BY [Year], EMPLOYEENAME

how to add row total and column total in this query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 00:46:53

what do you mean by row total and column total? can you show by means of sample data?
If its for a reporting application, you can very easily do this at front end in any reporting tool. Most reporting tools have this in built in them. Doing it in sql requires additional aggregations to be performed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghu_grdr
Starting Member

17 Posts

Posted - 2011-12-08 : 01:21:53

Jan feb march Total

10 10 10 30
10 20 10 40
10 10 10 30
Total 30 40 30 90

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 01:35:02

which is reporting tool you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghu_grdr
Starting Member

17 Posts

Posted - 2011-12-08 : 01:59:57

I am not using any reporting tool. I want to get this result using Query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 02:04:55

where are you showing this resultset then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghu_grdr
Starting Member

17 Posts

Posted - 2011-12-09 : 06:39:22

I am going to bind it with gridview using C#

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 08:36:07

[January]+[February]+[March]+.... will give you row totals

for column totals use WITH CUBE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghu_grdr
Starting Member

17 Posts

Posted - 2011-12-10 : 01:42:33

SELECT *
FROM(
SELECT
YEAR(calldate) [Year],
--COUNT(REQUESTS.reqid),
CASE MONTH(calldate)
-- WHEN 1 THEN 'January'
-- WHEN 2 THEN 'February'
-- WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
--WHEN 12 THEN 'December'
END as [Month],
--customerid,
-- SCHEDULE_EMPLOYEE.EMPID 'EMPID',
EMPLOYEE.EMPLOYEENAME 'EMPLOYEENAME',
REQUESTS.reqid

FROM requests
INNER JOIN SCHEDULE_EMPLOYEE ON SCHEDULE_EMPLOYEE.REQID = REQUESTS.REQID
INNER JOIN EMPLOYEE ON SCHEDULE_EMPLOYEE.EMPID = EMPLOYEE.EMPID
where YEAR(calldate) = 2011
) yearwise
--WorkOrders
PIVOT
(
count(reqid)
FOR [Month] IN (
--[January],[February],[March],
[April],
[May],[June],[July],[August],
[September],[October],[November],
[April]+[May]

-- ,[December]
)
) AS PivotTable

ORDER BY [Year], EMPLOYEENAME

When i try to add [April]+[May]
it thows me error incorrect syntax near +

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:44:45

it should be in select list


SELECT *,[April]+[May]+... as [total]
FROM(
SELECT
YEAR(calldate) [Year],
--COUNT(REQUESTS.reqid),
CASE MONTH(calldate)
-- WHEN 1 THEN 'January'
-- WHEN 2 THEN 'February'
-- WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
--WHEN 12 THEN 'December'
END as [Month],
--customerid,
-- SCHEDULE_EMPLOYEE.EMPID 'EMPID',
EMPLOYEE.EMPLOYEENAME 'EMPLOYEENAME',
REQUESTS.reqid

FROM requests
INNER JOIN SCHEDULE_EMPLOYEE ON SCHEDULE_EMPLOYEE.REQID = REQUESTS.REQID
INNER JOIN EMPLOYEE ON SCHEDULE_EMPLOYEE.EMPID = EMPLOYEE.EMPID
where YEAR(calldate) = 2011
) yearwise
--WorkOrders
PIVOT
(
count(reqid)
FOR [Month] IN (
--[January],[February],[March],
[April],
[May],[June],[July],[August],
[September],[October],[November]

-- ,[December]
)
) AS PivotTable

ORDER BY [Year], EMPLOYEENAME

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources