GET COMBINED SUM FROM GROUPED LEFT JOIN
dates table:
----------------
id, dates
----------------
1, 2016-06-01
2, 2016-06-02
3, 2016-06-03
4, 2016-06-04
5, 2016-06-05
batch_fixed table
--------------------------------------------------------
b_id,batch_group(1 or 2),status (0 to 4),subject_id(0 to 4), start_time(date time), end_time(date time)
--------------------------------------------------------
Expected Result
-----------------------------------------------------------
DATE combined_sum_1 combined_sum_2 combined_sum_3
-----------------------------------------------------------
2016-06-01 1525 2145 5415
2016-06-02 0 0 0
2016-06-03 1525 2145 5415
2016-06-04 1525 2145 5415
2016-06-05 0 0 0
I tried like following but it will not work as I am doing something wrong, kindly anyone give me an hint to construct a optimized query?
SELECT dates, DATEDIFF(dates.dates, NOW()) as date_diff,
IF(bf.batch_group=1,status=1,subject_id=1,sum(seconds),0) Mfree,
IF(bf.batch_group=1,status=2,subject_id=0,sum(seconds),0) MSub,
IF(bf.batch_group=1,status=3,subject_id=2,sum(seconds),0) MTest
FROM dates dates
LEFT JOIN (select batch_group,status,subject_id,sum(TIME_TO_SEC(TIMEDIFF(end_time, start_time))) as seconds from batch_fixed group by cdate,batch_group,status,subject_id) bf on bf.cdate=dates.dates
where dates.dates between '2016-06-01' AND '2017-06-23'
thanks in advance.
Prabakar.