find maxdate and mindate when grouped

Question

DB table

TYPE       DATE         PLAYER   MINUTE
---------------------------------------
starter    2000-01-01   Tom      
starter    2005-01-01   Tom      
starter    2005-01-01   Ben 
starter    2010-01-01   Tom
subbedin   2012-01-01   Ben      46

Query

$query = mysql_query("SELECT *,
SUM(CASE WHEN type = 'starter' OR type = 'subbedin' THEN 1 ELSE 0 END) AS apps,
SUM(CASE WHEN Tüüp = 'starter' THEN 90 ELSE 0 end + CASE WHEN Tüüp = 'subbedin' THEN 91 - minutes ELSE 0 end) AS minutes
FROM players
GROUP BY player
ORDER BY apps DESC minutes DESC
");

I've grouped the results by player alongside their appearances and minutes, but I'm having trouble getting it to show the date of the first and the last appearances.

PLAYER   APPS   MINUTES   FIRST        LAST
-------------------------------------------------
Tom      3      270       2000-01-01   2010-01-01
Ben      2      135       2005-01-01   2012-01-01

score 0 · Answer 1 · answered Jun 08 '12 at 11:08

Try this ....

SELECT
    SUM(CASE WHEN type = 'starter' OR type = 'subbedin' THEN 1 ELSE 0 END) AS apps,
    SUM(CASE WHEN type = 'starter' THEN 90 ELSE 0 end + CASE WHEN type = 'subbedin' THEN 91 - minutes ELSE 0 end) AS `minutes`,
    MIN(`DATE`) as `FIRST`,
    MAX(`DATE`) as `LAST`
    FROM players
    GROUP BY player;

find maxdate and mindate when grouped

1 Answers1