On 16 April 2014 20:52, steve-ALUG@hst.me.uk wrote:
I'm not sure, it's been a while since I've done SQL, but I think that the above will give the Max Indoor Temperature for the WHOLE TABLE and not just for a particular year, and repeat it once for each year in the table.
The whole point of GROUP BY should be to make sure that stuff like Min/Max/Count/etc apply to the values grouped, so this should work, AIUI.
But you made me doubt myself so I tested it:
==== test1.sql ==== CREATE DATABASE test1; USE test1; CREATE TABLE test1 ( Date datetime NOT NULL, IndoorTemperature float NOT NULL, OutdoorTemperature float NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO test1 (Date, IndoorTemperature, OutdoorTemperature) VALUES ('2014-04-17 10:53:23', 21.5, 15.8), ('2014-04-17 10:53:38', 22.5, 14.5), ('2012-04-17 10:53:23', 19.5, 13.8), ('2011-04-17 10:53:38', 20.5, 10.5);
SELECT Year(Date) as Year, Max(IndoorTemperature) as MaxTemp, Min(IndoorTemperature) as MinTemp FROM test1 GROUP BY Year(Date);
DROP DATABASE test1;
========
$mysql -uroot -p < test1.sql
Year MaxTemp MinTemp 2011 20.5 20.5 2012 19.5 19.5 2014 22.5 21.5