Q:

How to retrieve sql results with different calculated values for same column with join and group by?

Hi. Below i have written query to retrieve total-hours, last-month-total-hours and current-month-total-hours. All these are calculating from hours column of time_entries table and spent_on column of same table. Sorry if table formatting is not good.
Following three query is giving correct result.
Query#1
select p.name, 
FORMAT(sum(te.hours), 2) AS totalhours
from projects p
left join time_entries te on p.id = te.project_id
group by p.id

Result#1
name                     totalhours
----------------          ---------------

project A                 4932.18

project B 
                534.02
Query#2
select p.name, 
FORMAT(sum(te_last_mo.hours), 2) AS totalhours_last_mo
from projects p
left join time_entries te on p.id=te_last_mo.project_id
where te_last_mo.spent_on>=DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') and te_last_mo.spent_on<DATE_FORMAT(NOW() ,'%Y-%m-1')
group by p.id

Result#2
name                     total_hours_last_mo
----------------          ------------------------------

project A                 1726.72

project B
                157.75
Query#3
select p.name, 
FORMAT(sum(te_this_mo.hours), 2) AS totalhours_this_mo
from projects p
left join time_entries te_this_mo on p.id=te_this_mo.project_id
where te_this_mo.spent_on>=DATE_FORMAT(NOW() ,'%Y-%m-01') and te_this_mo.spent_on<DATE_FORMAT(NOW() ,'%Y-%m-31')
group by p.id

Result#3
name                     total_hours_this_mo
----------------          ------------------------------

project A                 421.19

project B 
                41.26
The above results and query are correct.
Now i want result like this but unable to figure out.
name             total_hours                        total_hours_last_mo                  total_hours_this_mo
------------      -----------------------------      -------------------------------             ------------------------------

project A             4932.18                            1726.72                                  421.19

project B             534.02                                 157.75                                    41.26
To combine these three hour columns i wrote query like this but throwing wrong result, may be because of join three times for same table.
select p.name, 
FORMAT(sum(te.hours), 2) AS totalhours,
FORMAT(sum(te_last_mo.hours), 2) AS totalhours_last_mo,
FORMAT(sum(te_this_mo.hours), 2) AS totalhours_this_mo

from projects p
left join time_entries te on p.id = te.project_id

left join time_entries te_last_mo on p.id = te_last_mo.project_id 
and te_last_mo.spent_on>=DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') and te_last_mo.spent_on<DATE_FORMAT(NOW() ,'%Y-%m-1')

left join time_entries te_this_mo on p.id = te_this_mo.project_id
where te_this_mo.spent_on>=DATE_FORMAT(NOW() ,'%Y-%m-01') and te_this_mo.spent_on<DATE_FORMAT(NOW() ,'%Y-%m-31')

group by p.id

Any solution would be appreciated. Thanks in advance.

A:

You may run the query with joins and no aggregations to see how those joins are working when used together and why that will lead to wrong results.
You can achieve the desired result by using one join and moving the criteria to aggregate calculations:
select p.name, 
FORMAT(sum(te.hours), 2) AS totalhours,
FORMAT(sum(
IF(spent_on>=DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') and spent_on<DATE_FORMAT(NOW() ,'%Y-%m-1'),
hours, NULL)
), 2) AS totalhours_last_mo,
FORMAT(sum(
IF(spent_on>=DATE_FORMAT(NOW() ,'%Y-%m-01') and spent_on<DATE_FORMAT(NOW() ,'%Y-%m-31'),
hours, NULL)
), 2) AS totalhours_this_mo
from projects p
left join time_entries te on p.id = te.project_id
group by p.id

