CODEWITHSUNDEEP
sqlsql-server
kk luo
kk luo

Reputation: 599

how to calculate sum value from group by

I have a table like

  person    type     date
  Tom       day     4/1/2018
  Tom      night    3/2/2018
  Tom       rest    4/3/2018
  Jack      day     4/1/2018
  Jack      day     4/2/2018
  Jack     night    4/3/2018
  Peter     day     4/1/2018
  Peter     day     3/2/2018
  Peter     day     4/3/2018

I want to count each one's working hours in April. Day shift is 8 hours and night is 11 hours. so the result is like 

  Person   hours
   Tom      8
   Jack     27
   Peter    16

I tried a SQL like 

select person,count(Type),
    case type when 'day' then count(type)*8 when 'night' then count(type)*11  
from table where date>'3/30/2018' 
group by person,type

and it work then I try to treat it like a table and add a group by outside like 

select * 
from (select person,count(Type),
    case type when 'day' then count(type)*8 when 'night' then count(type)*11  
    from table where date>'3/30/2018' group by person,type)

and it doesn't work. Why? any help apppreciated.

Upvotes: 3

Views: 62

Answers (3)

Sahi
Sahi

Reputation: 1484

try this:

SELECT person,
SUM(Case WHEN type='day' THEN 8
     WHEN type='night' THEN 11
     ELSE 0 END)
FROM Table1
WHERE [date]>'2018/03/30'
Group by person

SQL Fiddle: http://sqlfiddle.com/#!18/5b946/1

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

The key to get your result is to use case statement to "convert" type to appropriate amount of hours. Try this:

select person, SUM([hrs]), [date] from (
    select person,
           case [TYPE_ID] when 'day' then 8
                          when 'night' then 11
                      else 0 end [hrs],
           [date]
    from MY_TABLE
    where date between '4/01/2018' and '5/01/2018'
) [a] group by person

Upvotes: 1

fthiella
fthiella

Reputation: 49049

You could use the following case-when to convert the type to the corrisponding number of hours:

case
  when type='day' then 8
  when type='night' then 11
end

then you can just sum the corresponding number of hours:

select
  person,
  sum(
    case
      when type='day' then 8
      when type='night' then 11
    end
  ) as hours
from
  table_name
where
   date>='4/01/2018' and date<'5/01/2018'
group by
  person

Upvotes: 3

Related Questions