single sql query to perform some group by

group-by, mysql, sql

Solution

SELECT *
FROM (
  SELECT name, 
    DATEDIFF(MAX(date), MIN(date)) total_days,
    (SELECT tt.status FROM projects tt 
     WHERE t.name = tt.name AND tt.date = MAX(t.DATE)) latest_status,
    (SELECT tt.priority FROM projects tt 
     WHERE t.name = tt.name AND tt.date = MAX(t.DATE)) latest_priority
  FROM projects t
  GROUP BY name 
  ) t
WHERE latest_status != 'closed'
ORDER BY (CASE latest_priority
          WHEN 'high' THEN 0
          WHEN 'medium' THEN 1
          WHEN 'low' THEN 2 
          END), name;

- total days: take the `DATEDIFF` of the `MAX` and `MIN` date, which will give you the number of days in between;

- latest status: fetch the status for which the row's date is equal to the `MAX` date;

- latest priority: fetch the priorityfor which the row's date is equal to the `MAX` date;

- order by: translate each priority string to a numerical value and order by it.

Here's an sqlfiddle.

Problem

``` CREATE TABLE IF NOT EXISTS `projects` ( `idproject` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `date` datetime NOT NULL, `status` enum('new','active','closed') NOT NULL, `priority` enum('low','medium','high') NOT NULL, PRIMARY KEY (`idproject`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ``` Here are some data: ``` INSERT INTO `projects` (`idproject`, `name`, `date`, `status`, `priority`) VALUES (1, 'iCompany', '2011-03-23 11:41:44', 'new', 'medium'), (2, 'John Doe & Co.', '2011-04-09 14:38:04', 'closed', 'low'), (3, 'ACME, Inc.', '2011-05-21 11:43:11', 'active', 'high'), (4, 'John Doe & Co.', '2011-03-28 15:19:45', 'active', 'low'), (5, 'John Doe & Co.', '2011-03-08 15:16:32', 'new', 'low'), (6, 'ACME, Inc.', '2011-04-05 20:58:42', 'active', 'low'), (7, 'Mega Corp', '2011-04-21 08:08:53', 'new', 'low'), (8, 'iCompany', '2011-04-17 08:40:36', 'active', 'medium'), (9, 'iCompany', '2011-05-18 14:36:48', 'active', 'low'), (10, 'John Doe & Co.', '2011-04-18 19:08:25', 'new', 'medium'), (11, 'ACME, Inc.', '2011-05-19 13:11:04', 'active', 'low'), (12, 'Foo Bars', '2011-03-03 17:19:29', 'new', 'high'), (13, 'ACME, Inc.', '2011-04-23 20:42:33', 'active', 'medium'), (14, 'Foo Bars', '2011-05-13 09:18:15', 'active', 'medium'), (15, 'ACME, Inc.', '2011-03-20 14:37:18', 'new', 'low'), (16, 'Foo Bars', '2011-04-18 13:46:23', 'active', 'high'), (17, 'iCompany', '2011-05-31 07:13:32', 'closed', 'low'), (18, 'Foo Bars', '2011-05-31 15:43:39', 'active', 'low'), (19, 'John Doe & Co.', '2011-05-28 11:28:32', 'active', 'medium') ``` I'd like to have the list of all projects: - with their latest (chronologically) status and priority, - with the number of days between the first and latest entry (0 if there is only one entry), you may ignore the hours, - sorted by by priority ('high' first), then by name, - without the projects where the latest status is 'closed' (omitted from result). Output should be: ``` +---------------+-----------+---------------+----------------+ ¦name ¦total_days ¦latest_status ¦latest_priority ¦ +---------------+-----------+---------------+----------------+ ¦ACME, Inc. ¦62 ¦active ¦high ¦ ¦John Doe & Co. ¦81 ¦active ¦medium ¦ ¦Foo Bars ¦89 ¦active ¦low ¦ ¦Mega Corp ¦0 ¦new ¦low ¦ +---------------+-----------+---------------+----------------+ ``` So far i got to write this: ``` SELECT name,status FROM projects group by name order by priority desc,name ``` please help?

Original source