gregor-multiple-group-bys-sort-by-sumd-group-values.sql

-- http://stackoverflow.com/questions/3585467/multiple-group-bys-sort-by-sumd-group-values

SELECT 
  project_name, service_name, project_minutes, minutes
FROM  
  (
    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 
    LEFT OUTER JOIN `services` ON `services`.id = `time_entries`.service_id 
    WHERE date_at = '2010-08-27'
    GROUP BY 
      time_entries.project_id, 
      time_entries.service_id
  ) as group2
LEFT JOIN 
  (
    SELECT project_id as project_id, sum(minutes) as project_minutes 
    FROM 
      (
        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 
        LEFT OUTER JOIN `services` ON `services`.id = `time_entries`.service_id 
        WHERE date_at = '2010-08-27'
        GROUP BY 
          time_entries.project_id, 
          time_entries.service_id
      ) as group2
    GROUP BY project_id         
  ) as group1  on group1.project_id = group2.project_id
ORDER BY 
  project_minutes DESC, 
  minutes DESC;

michael-multiple-group-bys-sort-by-sumd-group-values.sql

SELECT
  t.project_name, t.service_name, p.minutes AS project_minutes, t.minutes
FROM
  (
    SELECT                  
      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.project_id, 
      time_entries.service_id    
  ) t
JOIN
  (
    Select 
      project_Id, Sum(minutes) minutes
    FROM 
      time_entries
    GROUP BY project_id
  ) p
ON (p.project_id = t.project_id)
ORDER BY     
  project_minutes DESC, minutes DESC;  

symcbean1-multiple-group-bys-sort-by-sumd-group-values.sql

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

symcbean2-multiple-group-bys-sort-by-sumd-group-values.sql

SELECT 
  ilv1.project_name, ilv1.service_name, ilv2.minutes AS project_minutes, ilv1.minutes
FROM 
  (
    SELECT      
      p1.name as project_name,
      s1.name as service_name,
      p1.id as project_id,  
      s1.id as service_id,  
      SUM(minutes) AS minutes 
    FROM time_entries te1             
    JOIN projects p1 ON p1.id = te1.project_id 
    JOIN services s1 ON s1.id = te1.service_id 
    GROUP BY 
    te1.project_id, te1.service_id
  ) AS ilv1,
  (
    SELECT 
      project_id, ilv3.project_name, sum(ilv3.minutes) as minutes 
    FROM 
    (
      SELECT      
        p1.id as project_id,
        p1.name as project_name,
        s1.name as service_name,
        SUM(minutes) AS minutes 
      FROM time_entries te1             
      JOIN projects p1 ON p1.id = te1.project_id 
      JOIN services s1 ON s1.id = te1.service_id 
      GROUP BY 
      te1.project_id, te1.service_id
    ) ilv3
   GROUP BY ilv3.project_id
  ) ilv2
WHERE ilv1.project_id=ilv2.project_id 
ORDER BY ilv2.minutes;