Multiple SUM's in a SELECT but on one view

user_10679041 0
Viewed 73

I've written a select statement to assist with timesheet approval. It show's the summation of hours entered against a user based on different roles booked, i.e. Standard Office Hours, Overtime, Holiday & Offshore.

I want a single row per [Name] but I get a row for every SUM where the user has data.

Do I need to create this as a view and SELECT to that view?

My current code is below:


SELECT 
    TSDEPT_HEAD As [Resource],
    CONCAT (PP_FIRST_NAME,' ',PP_SURNAME) As [Name],    
    SUM(Case WHEN TST_ROLE = 'STD' Then TST_HOURS END) As STD, 
    SUM(Case WHEN TST_ROLE LIKE 'OT%' Then TST_HOURS END) As OT,
    SUM(Case WHEN TST_ROLE LIKE 'OFF%' Then TST_HOURS END) As OFFSHORE,
    SUM(Case WHEN TST_ROLE = 'HOL' Then TST_HOURS END) As HOL
    FROM TS_TIMESHEETS
LEFT JOIN SYS_PEOPLE on TST_RESOURCE = PP_CODE
LEFT JOIN TS_USERS on TST_RESOURCE = TSU_USERID
LEFT JOIN TS_DEPARTMENTS on TSU_DEPARTMENT_CODE = TSDEPT_CODE
WHERE MONTH(TST_DATE) = MONTH(GETDATE())
GROUP BY TSDEPT_NAME,TST_ROLE,PP_FIRST_NAME,PP_SURNAME,TST_RESOURCE,PP_KNOWN_AS,TSDEPT_HEAD
sql-server sql
2 Answers

You should just use the group by for the columns you really want aggregate, tipilcally the same columns you have in select but not with an aggreagtion functions

SELECT 
TSDEPT_HEAD As [Resource],
CONCAT (PP_FIRST_NAME,' ',PP_SURNAME) As [Name],    
SUM(Case WHEN TST_ROLE = 'STD' Then TST_HOURS END) As STD, 
SUM(Case WHEN TST_ROLE LIKE 'OT%' Then TST_HOURS END) As OT,
SUM(Case WHEN TST_ROLE LIKE 'OFF%' Then TST_HOURS END) As OFFSHORE,
SUM(Case WHEN TST_ROLE = 'HOL' Then TST_HOURS END) As HOL
FROM TS_TIMESHEETS
LEFT JOIN SYS_PEOPLE on TST_RESOURCE = PP_CODE
LEFT JOIN TS_USERS on TST_RESOURCE = TSU_USERID
LEFT JOIN TS_DEPARTMENTS on TSU_DEPARTMENT_CODE = TSDEPT_CODE
WHERE MONTH(TST_DATE) = MONTH(GETDATE())
GROUP BY     TSDEPT_HEAD, CONCAT (PP_FIRST_NAME,' ',PP_SURNAME)
user_35223120

GROUP BY [Name] only - the sum statements are aggregates, so don't need to be included in the group clause. This will give you one row for each person, based on the [Name] column.

user_151676540
Related