get a sum of grouped recored

Question

First of all here it the SQL Fiddle Demo.

now you can see in fiddle i have two table named project and working and now i have grouped project which are grouped together with having same name except second table have _QA postfix.

Which means i have successfully implemented query which allows me to group that project but now i also need to SUM planned hours for that two project.

If you see in fiddle its coming 1000 but it must be 2500 meaning sum result of both project and project_QA

Thanks.

Answer 1

My try.... :

SELECT p.name,
  (SELECT SUM(p1.planned_hour)
   FROM project p1
   WHERE REPLACE(p1.name,'_QA','') = REPLACE(p.name,'_QA','') ) AS ProjectPlannedHour,
  SUM( ROUND( TIME_TO_SEC( TIMEDIFF( vv.end_date, vv.start_date ) ) / 3600 ,2)) AS TotalHour
FROM project p
INNER JOIN working vv ON vv.project_id = p.id
GROUP BY  REPLACE(p.name,'_QA','')
ORDER BY TotalHour DESC
LIMIT 0,5

With Join:

SELECT p.name,
       p1.planned_hour AS ProjectPlannedHour,
       SUM(ROUND(TIME_TO_SEC(TIMEDIFF(vv.end_date, vv.start_date)) / 3600 ,2)) AS TotalHour
FROM project p
INNER JOIN working vv ON vv.project_id = p.id
INNER JOIN (SELECT REPLACE(name,'_QA','') AS `name`,
                   SUM(planned_hour) AS planned_hour
                   FROM project
                   GROUP BY REPLACE(name,'_QA','')) p1 
         ON REPLACE(p1.name,'_QA','') = REPLACE(p.name,'_QA','')
GROUP BY REPLACE(p.name,'_QA','')
ORDER BY TotalHour DESC LIMIT 0, 5

Result:

|    NAME | PROJECTPLANNEDHOUR | TOTALHOUR |
|---------|--------------------|-----------|
| project |               2500 |      4.67 |

Maybe somebody give better solution, I yet cant figurout more simple query...