MySQL Forums
Forum List  »  General

GET COMBINED SUM FROM GROUPED LEFT JOIN
Posted by: Praba Karan
Date: July 15, 2016 08:27AM

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.

Options: ReplyQuote


Subject
Written By
Posted
GET COMBINED SUM FROM GROUPED LEFT JOIN
July 15, 2016 08:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.