CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Erika Danis
Erika Danis

Reputation: 17

Cannot perform an aggregate function on an expression containing an aggregate or a subquery when using SUM in CASE WHEN

This SQL query fails when i try and do a

"WHEN SUM(CONVERT(decimal(10,2),DATEDIFF(second, t.Start, t.Finish ) / 3600.0)) > 8"

in my Case Statement.

SELECT e.EmployeeNumber, e.FirstName, e.LastName, cast(t.DateWorked as date) as DateWorked,
SUM(CONVERT(decimal(10,2),DATEDIFF(second, t.Start, t.Finish ) / 3600.0)) as HoursWorked,
SUM(CASE 
-- #Check for overtime# HoursWorked > DailyHours
WHEN SUM(CONVERT(decimal(10,2),DATEDIFF(second, t.Start, t.Finish ) / 3600.0)) > 8
    THEN 
        1
ELSE
        0
END) as moneyEarned
FROM Timesheets t, Employees e
WHERE cast(t.DateWorked as date) BETWEEN @datefrom AND @dateto
AND t.IsDeleted = 0
AND t.EmployeeId = e.EmployeeId
AND t.FarmId = @farmid
GROUP BY e.EmployeeNumber, e.FirstName, e.LastName, cast(t.DateWorked as date)

Is there a way that i could access "HoursWorked" as a variable in my Case statement?

Currently i am getting a "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." error.

Any pointers of how i could approach this differently would be greatly appreciated.

Upvotes: 0

Views: 2184

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You have a sum() inside a sum() and this is not allowed. Presumably, you intend:

SELECT e.EmployeeNumber, e.FirstName, e.LastName,
       cast(t.DateWorked as date) as DateWorked,
       SUM(CONVERT(decimal(10,2),DATEDIFF(second, t.Start, t.Finish ) / 3600.0)) as HoursWorked,
       (CASE -- #Check for overtime# HoursWorked > DailyHours
             WHEN SUM(CONVERT(decimal(10,2),DATEDIFF(second, t.Start, t.Finish ) / 3600.0)) > 8
             THEN 1 ELSE 0
       END) as moneyEarned
. . .

You should also learn to use proper explicit join syntax.

Upvotes: 1

Related Questions