CODEWITHSUNDEEP
sqlsql-server
Saiyanthou
Saiyanthou

Reputation: 142

Multiple SUM's in a SELECT but on one view

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

Upvotes: 2

Views: 156

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

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)

Upvotes: 4

Marshall
Marshall

Reputation: 19

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.

Upvotes: 1

Related Questions