Cully Mason Cully Mason - 1 year ago 54
SQL Question

How do I add a where clause to a sum aggregate?

I am trying to figure out best way to get the aggregate of a person's hours spent on a project name that follows a certain pattern

Current Tables

| Tbl_Employee | Tbl Projects | tbl_timesheet |
| employee_id | project_id | timesheet_id |
| employee_full_name | cws_project_id | employee_id |
| | | project_id |
| | | timesheet_hours |

Here is the query I have so far

sum(tt.timesheet_hours) as total_hours,
month(tt.timesheet_date) as "month",
year(tt.timesheet_date) as "year"

from tbl_employee te
left join tbl_timesheet tt
on te.employee_id = tt.employee_id
join tbl_projects tp
on tp.project_id = tt.project_id
where te.employee_active = 1
and te.employee_id > 0
and employee_department IN ("Project Management","Engineering","Deployment Srvs.")
and year(tt.timesheet_date) = 2015
group by te.employee_last_name, year(tt.timesheet_date), month(tt.timesheet_date)
order by employee_last_name

What I need to add to my select statement is something to the effect of

sum(tt.timesheet_hours) as where cws_project_id like '%Training%' as training

In short I need to know the sum of hours an employee has contributed to a project where the
contains the word Training. I know you cant add a where clause to a Sum but I cant seem to find another way to do it.

If this makes a difference I need to do this several times - ie where the project_name contains a different word.

Thank you so much for any help that can be provided. I hope that is not clear as mud.

Answer Source

Here is the general form of what you are looking for:

SELECT SUM(IF(x LIKE '%y%', z, 0)) AS ySum

even more general

SELECT SUM(IF([condition on row], [value or calculation from row], 0)) AS [partialSum]

Edit: For more RDBMS portability (earlier versions of MS SQL do not support this form of IF):

SELECT SUM(CASE WHEN [condition on row] THEN [value or calculation from row] ELSE 0 END) AS [partialSum]