CODEWITHSUNDEEP
mysql
Cully Mason
Cully Mason

Reputation: 463

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

select
te.employee_id, 
te.employee_last_name,
te.employee_first_name,
te.employee_department,
te.employee_type_id,
te.timesheet_routing,
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 cws_project_id 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.

Upvotes: 0

Views: 29

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

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]

Upvotes: 1

Related Questions