CODEWITHSUNDEEP
sqlsql-server
CodeWarrior
CodeWarrior

Reputation: 538

SQL different aggregates in single query

I am trying to work out a query where I have a table representing the following:

enter image description here

and I need the result to indicate the earliest start time (blue), the latest end time (green), and the sum of the lunch breaks (yellow):

enter image description here

I got the blue and green blocks right, but are struggling with the yellow aggregation.

My (partial) query looks like:

select
    Name,
    min(StartTime) StartTime,
    max(EndTime) EndTime,
    sum( <please help here with alternative to this aggregation> ) Lunch
from
    SomeTable
group by
    Name

When I use a normal subquery, SQL complains that the column is not contained in either a "group by" or aggregate, and I cannot use a subquery inside an aggregate.

Please point me into a direction for the "lunch" column.

This is on SQL Server.

Upvotes: 0

Views: 60

Answers (2)

alaa_sayegh
alaa_sayegh

Reputation: 2211

would suggest you to use case when like this: sum(case when activity = 'lunch' then duration end) as break

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271181

Assuming the value is a time, then the sum is a little challenging. I would suggest converting to minutes:

select Name, min(StartTime) as StartTime, max(EndTime) as EndTime,
       sum(case when activity = 'lunch'
                then datediff(minute, 0, duration)
           end) as lunch_minutes
from SomeTable
group by Name

Upvotes: 4

Related Questions