The set of GROUP BY & sort values ​​by SUM'd

Question

I am working on reports for our time tracking application. Each time a record is associated with a project and service. Here is a simplified query for grouping time records by project and service.

SELECT                    
  projects.name as project_name,
  services.name as service_name,
  SUM(minutes) AS minutes 
FROM `time_entries`             
JOIN `projects` ON `projects`.id = `time_entries`.project_id 
JOIN `services` ON `services`.id = `time_entries`.service_id 
GROUP BY 
  time_entries.project_id, 
  time_entries.service_id    
ORDER BY
  max(minutes)   DESC

This will result in a table like this:

+---------------+--------------+---------+
| project_name  | service_name | minutes |
+---------------+--------------+---------+
| Business Card | Consulting   |    4800 |
| Microsite     | Coding       |    3200 |
| Microsite     | Consulting   |    2400 |
| Microsite     | Design       |    2400 |
| Business Card | Design       |     800 |
+---------------+--------------+---------+

What I'm trying to achieve is the ability to sort by SUM'd protocols. Not the Business Card project should be on top, but the Microsite project, because it has more minutes.

+---------------+--------------+-----------------+---------+
| project_name  | service_name | project_minutes | minutes |
+---------------+--------------+-----------------+---------+
| Microsite     | Coding       |            8000 |    3200 |
| Microsite     | Consulting   |            8000 |    2400 |
| Microsite     | Design       |            8000 |    2400 |
| Business Card | Consulting   |            5600 |    4800 |
| Business Card | Design       |            5600 |     800 |
+---------------+--------------+-----------------+---------+

The only way I found to get the "project_minutes" column is to create the table first and combine it with myself. In the request, I came up with:

DROP TABLE IF EXISTS group2;    
CREATE TABLE group2     SELECT                     
  projects.id as project_id,
  projects.name as project_name,
  services.name as service_name,
  SUM(minutes) AS minutes 
FROM `time_entries`             
JOIN `projects` ON `projects`.id = `time_entries`.project_id 
JOIN `services` ON `services`.id = `time_entries`.service_id 
GROUP BY 
  time_entries.project_id, 
  time_entries.service_id    
ORDER BY
  max(minutes)   DESC
LIMIT 0, 30;

SELECT 
  project_name, service_name, project_minutes, minutes
FROM  
  group2
LEFT JOIN 
  (
    SELECT project_id as project_id, sum(minutes) AS project_minutes
      FROM group2
     GROUP BY project_id         
  ) as group1  on group1.project_id = group2.project_id
ORDER BY 
  project_minutes DESC, 
  minutes DESC;    

- mySQL (?): http://www.google.com/search?&q=site:bugs.mysql.com+reopen+temporary+table

:

• "project_minutes", SUMs ? SQL, ?
• , , ? , ? Rails, .

!

UPDATE

. , : http://gist.github.com/553560

, , time_entries ? , - :

• time_entries , (~ 4 )
• 6 . : http://dl.dropbox.com/u/732913/time_entries_grouped_by_customer_project_service_user.png

Answer 1

- , :

SELECT ilv1.date_at, ilv1.project_name, ilv1.service_name, ilv1.minutes
FROM 
( SELECT                             
  te1.date_at,
  p1.name as project_name,
  s1.name as service_name,
  SUM(minutes) AS minutes 
FROM time_entries te1             
LEFT OUTER JOIN projects p1 ON p1.id = te1.project_id 
LEFT OUTER JOIN services s1 ON s1.id = te1.service_id 
GROUP BY 
  te1.project_id, 
  te1.service_id) AS ilv1,
( SELECT                             
  te2.date_at,
  p2.name as project_name,
  SUM(minutes) AS minutes 
FROM time_entries te1             
LEFT OUTER JOIN projects p1 ON p1.id = te1.project_id  
GROUP BY 
  te1.project_id) AS ilv2

WHERE ilv1.date_at = ilv2.date_at  AND ilv1.project_name = ilv2.project_name  ORDER BY ilv2.minutes;

( - )

, ( , ). , , .

SELECT ilv1.date_at, ilv1.project_name, ilv1.service_name, ilv1.minutes
FROM 
 (....) ilv1,
 (SELECT ilv3.date_at, ilv3.project_name, sum(ilv3.minutes) as minutes 
  FROM (...copy of ilv1) ilv3
  GROUP BY ilv3.date_at, ilv3.project_name
 ) ilv2
WHERE ilv1.date_at=ilv2.date_at

ilv1.project_name = ilv2.project_name  ORDER BY ilv2.minutes;

.

Answer 2

, project_id time_entries NULL, services_id null

Select t.date, t.project_name, t.service_name, p.minutes as Project_minutes, t.minutes
FROM
(SELECT                             
  time_entries.date_at,
  time_entries.project_Id,
  projects.name as project_name,
  services.name as service_name,
  SUM(minutes) AS minutes 
FROM time_entries             
JOIN projects ON projects.id = time_entries.project_id 
LEFT JOIN services ON services.id = time_entries.service_id 
GROUP BY 
  time_entries.date_at
  time_entries.project_id, 
  time_entries.service_id    
) t
JOIN
  (Select date_at, project_Id, Sum(minutes) minutes
  from time_entries
  group by date_at, project_id) p
ON (p.date_at = t.date_at AND p.project_id = t.project_id)