SUM of Columns - 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

SUM of Columns

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-05-24 : 13:39:01

I have the following query:


SELECT
    ProjectId, 
    ISNULL([Sunday], 0) AS Sunday, 
    ISNULL([Monday], 0) AS Monday, 
    ISNULL([Tuesday], 0) AS Tuesday, 
    ISNULL([Wednesday], 0) AS Wednesday, 
    ISNULL([Thursday], 0) AS Thursday, 
    ISNULL([Friday], 0) AS Friday, 
    ISNULL([Saturday], 0) AS Saturday
FROM
    (SELECT
        TS.ProjectId,
	dbo.GetWeekDayNameOfDate(TS.Date) AS [Day],
        TS.Hours AS [HOURS] 
     FROM
	Timesheet T,
	TimesheetSegment TS
     WHERE
	T.EmployeeId = 4 AND
	T.StartDate = '2012-05-20' AND
	TS.TimesheetId = T.ID
    GROUP BY
	TS.ProjectId, dbo.GetWeekDayNameOfDate(TS.Date), TS.Hours
	) 
p PIVOT ( SUM(Hours) FOR [DAY] IN ( [Sunday], [Monday], [Tuesday], 
                                          [Wednesday], [Thursday], 
                                          [Friday], [Saturday]) ) AS PivotTable

This will return something like this:


ProjectId   Sunday   Monday   Tuesday   Wednesday.......
    2        0.00     5.00      3.00       5.00  ........

How can I add a Total Column to the end and add up all the hours from Sunday to Saturday?

Like so:


ProjectId   Sunday   Monday   Tuesday   Wednesday .... Total
    2        0.00     5.00      3.00       5.00   .... 13.00

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-24 : 13:45:36

[code]SELECT
ProjectId,
ISNULL([Sunday], 0) AS Sunday,
ISNULL([Monday], 0) AS Monday,
ISNULL([Tuesday], 0) AS Tuesday,
ISNULL([Wednesday], 0) AS Wednesday,
ISNULL([Thursday], 0) AS Thursday,
ISNULL([Friday], 0) AS Friday,
ISNULL([Saturday], 0) AS Saturday,
ISNULL([Sunday], 0) +
ISNULL([Monday], 0) +
ISNULL([Tuesday], 0) +
ISNULL([Wednesday], 0) +
ISNULL([Thursday], 0) +
ISNULL([Friday], 0) +
ISNULL([Saturday], 0) AS Total
FROM
(SELECT
TS.ProjectId,
dbo.GetWeekDayNameOfDate(TS.Date) AS [Day],
TS.Hours AS [HOURS]
FROM
Timesheet T,
TimesheetSegment TS
WHERE
T.EmployeeId = 4 AND
T.StartDate = '2012-05-20' AND
TS.TimesheetId = T.ID
GROUP BY
TS.ProjectId, dbo.GetWeekDayNameOfDate(TS.Date), TS.Hours
)
p PIVOT ( SUM(Hours)
FOR [DAY] IN ( [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday]) ) AS PivotTable[/code]By the way, you should consider using DATENAME(dw,TS.Date) instead of your UDF call, it will perform better on large data sets.

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-05-24 : 13:54:21

Perfect!
I will change to DateName as well.

Thanks!!

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-05-25 : 12:18:01

Would it also be possible to have a Total Hours Row?
So the results would look something like this:


ProjectId   Sunday   Monday   Tuesday   Wednesday .... Total
    2        0.00     5.00      3.00       5.00   .... 13.00
    5        0.00     3.00      5.00       3.00   .... 11.00
Total Hours  0.00     8.00      8.00       8.00   .... 24.00

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-25 : 13:56:16

Take a look at ROLLUP and GROUPING SETS (SQL 2008+). It would take some finagling with PIVOT but should be possible. Also look at the GROUPING() function, you can use that in a CASE expression to identify the summary rows and apply the "Total Hours" value to it.

X002548
Not Just a Number

15586 Posts

Posted - 2012-05-26 : 01:48:56

Take a look at using ANSI Standard Functions, like COALESCE instead of ISNULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources