CODEWITHSUNDEEP
sqlsql-server
arcee123
arcee123

Reputation: 243

Group by with case statement fails group by clause

I have a table:

PROCEDURE
---------
id  INT
SERVICE_TYPE  VARCHAR(20)
TIMEUNITS (INT)
UNITS (INT)

I am trying to run this sql code:

select SERVICE_TYPE, 
       case when TIMEUNITS > 1 THEN SUM(1) 
       WHEN UNITS > 1 THEN SUM(UNITS) 
       END
group by SERVICE_TYPE

My problem is that I"m told that TIMEUNITS is not grouped nor aggregated by...

how do I repair this query? thanks

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

For conditional aggregation, the case expression is the argument to the aggregation function. For example, to get the number of rows where timeunits > 1:

select SERVICE_TYPE,
       sum(case when timeunits > 1 then 1 else 0 end)
from t
group by SERVICE_TYPE;

Upvotes: 2

Related Questions