SQL Sums with Where Clauses

I have a Table of “hours” that I am trying to create a SQL query to total some sums depending on a couple variables.

In my table there are HOURS, Location, User, Activity. (with many other fields)

I want to Total the sums of

Sum(Hours) AS TotalHours, Sum(Hours where Location=Place1) AS OffisteHours, Sum(Hours where Location=Place2) As OnsiteHours Group it by Activity then by User.

I am not having any issues doing the group by’s, just having issues getting sums.

the result I am looking for would be something like this.

NAME               OFFISTEHOURS      ONSITEHOURS      TOTAL HOURS
JohnSmith            23                   17                40
HarryBolz            11                   33                44
LacyWeir             12                   23                45

I have no trouble creating a query with a SUM, I just keep running into brick walls adding a second SUM with a Clause.

Any pointers into the right direction would be helpful ! Thanks!

Answer

You could try using a conditional aggregation In MSACCESS you can use IFF for conditional aggreagation

 select User
    , Sum(Hours)
    , sum(IIF(Location ='Place1' , Hours, 0)) OFFISTEHOURS      
    , sum(IIF(Location ='Place2' , Hours, 0)) ONSITEHOURS      
from m_table  
group by  User