CODEWITHSUNDEEP
phpmysqlgroup-by
Dipesh Parmar
Dipesh Parmar

Reputation: 27364

get a sum of grouped recored

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.

Upvotes: 5

Views: 110

Answers (2)

Devart
Devart

Reputation: 121902

Try another solution - 

SELECT GROUP_CONCAT(NAME) name, SUM(planned_hour) planned_hour, SUM(TotalHour) TotalHour FROM (
  SELECT
    p.name,
    p.planned_hour,
    SUM(TIMESTAMPDIFF(SECOND, vv.start_date, vv.end_date)) / 3600 TotalHour
  FROM
    project p
  INNER JOIN
    working vv ON vv.project_id = p.id
  GROUP BY
      p.id) t;

It is a bit simplified. The result is:

project,project_QA  2500    4,6667

Upvotes: 0

Justin
Justin

Reputation: 9724

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...

Upvotes: 3

Related Questions