I think there should be a way to do this but at the moment I can't see how, I always find SQL difficult to fathom though.
I have a table with dates, times and temperatures in it. I want to select the maximum temperature for each year, how do I do that?
The table has columns as follows:- ID Date Time Indoor Temperature Outdoor Temperature
What I want to get out of it is something like:- 2014 21.23 2013 30.45 2012 25.31
It's a sqlite3 database so there's no procedural language easily available. I guess I could write a Python script to do what I want, that might be the easiest way.
On 14/04/14 13:27, Chris Green wrote:
I think there should be a way to do this but at the moment I can't see how, I always find SQL difficult to fathom though.
I have a table with dates, times and temperatures in it. I want to select the maximum temperature for each year, how do I do that?
The table has columns as follows:- ID Date Time Indoor Temperature Outdoor Temperature
What I want to get out of it is something like:- 2014 21.23 2013 30.45 2012 25.31
It's a sqlite3 database so there's no procedural language easily available. I guess I could write a Python script to do what I want, that might be the easiest way.
I'm quite rusty, but if you were doing this just in SQL, firstly, you need a list of years
SELECT Year(Date) as TheYear from TheTable Order by TheYear Desc Group by TheYear;
There will be some function that returns the year. I don't know exactly what it will be. You want it to return a number rather than a string. If it returns a string, you may run into problems. The "as TheYear" creates a calculated field. Hopefully your dialect of SQL will allow you to sort and group by this field. Order by ...Desc makes the numbers go down. Group by means that you'll get one entry for each unique year.
Actually, revise that, skip the Order by for now. TheTable is the name of your table.
SELECT Year(Date) as TheYear from TheTable Group by TheYear;
If that works... Now you need get the year and temp thusly
SELECT Max([Indoor Temperature]) as MaxInTemp, Year(Date) as TheYear FROM TheTable Where TheYear in { (SELECT Year(Date) as TheYear from TheTable Group by TheYear) } Order By TheYear Desc;
Max is a calculation field which finds the max value of a field (Field is a name for one of your table entries - ID, Date etc) Indoor Temperature is included in [ ] because it includes a space. You may need to use some other escaping to include a field name with a space in it. Others include { }, ", ' or other varieties of '. Best to avoid spaces in field names! Also, Date is likely to be a reserved word of function, so best avoided - use something like SampleDate perhaps. If you don't, you may need to dereference it with something like TheTable.Date.
"In" is a feature not supported by all flavours of SQL. It lets you do stuff like SELECT SampleDate From TheTable where ID in {1, 3, 5} which would return the Sampledate for records 1, 3 and 5. (Select Year(Date)....) will give you an unsorted list of years Finally, sort by TheYear, descending order. (Desc may be descending).
There will be many other ways of doing this. For instance, if using Microsoft Tools to do this in M$ Access or Visual Basic, you could get a list of records (recordset) with the years in it, then run an SQL query to find the max temp for that year, then repeat with the next year etc.
Names of functions, restrictions on field names and language syntax varies between different versions of SQL though, so you'll have to experiment. I hope that the above will give you an idea though.
Steve
On 14 April 2014 13:27, Chris Green cl@isbd.net wrote:
I have a table with dates, times and temperatures in it. I want to select the maximum temperature for each year, how do I do that?
I don't know what sqlite is capable of and this isn't tested but I'd be looking at something like: SELECT Year(Date) as Year, MAX(IndoorTemperature) as MaxIndoor, MAX(OutdoorTemperature) as MaxOutdoor FROM MyTable GROUP BY Year(Date)
On 15/04/14 16:37, Mark Rogers wrote:
I don't know what sqlite is capable of and this isn't tested but I'd be looking at something like: SELECT Year(Date) as Year, MAX(IndoorTemperature) as MaxIndoor, MAX(OutdoorTemperature) as MaxOutdoor FROM MyTable GROUP BY Year(Date)
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.
E.g. if your data was ID Temp Date 1 10 1/1/2000 2 20 1/1/2001 3 30 1/1/2002
I think SQL like
SELECT Year(Date) as Year, MAX(Temp) as MaxTemp, FROM MyTable GROUP BY Year(Date)
would give
30 2000 30 2001 30 2002
Which is why I did the subquery bit in my attempt!
Good luck. :-) Steve
On Wed, Apr 16, 2014 at 08:52:33PM +0100, steve-ALUG@hst.me.uk wrote:
On 15/04/14 16:37, Mark Rogers wrote:
I don't know what sqlite is capable of and this isn't tested but I'd be looking at something like: SELECT Year(Date) as Year, MAX(IndoorTemperature) as MaxIndoor, MAX(OutdoorTemperature) as MaxOutdoor FROM MyTable GROUP BY Year(Date)
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.
E.g. if your data was ID Temp Date 1 10 1/1/2000 2 20 1/1/2001 3 30 1/1/2002
I think SQL like
SELECT Year(Date) as Year, MAX(Temp) as MaxTemp, FROM MyTable GROUP BY Year(Date)would give
30 2000 30 2001 30 2002
Which is why I did the subquery bit in my attempt!
Yes, I think it probably needs a subquery, I'll persevere. Thanks for the ideas everyone.
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
On Thu, Apr 17, 2014 at 11:03:56AM +0100, Mark Rogers wrote:
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:
Yes, worked perfectly for me too, thanks Mark. It even allows me to select max/min inside and outside temperatures for each year, very neat.
I have another sqlite3 specific question now (goes on from the above), I've started a new thread for it.
On 17/04/14 11:03, Mark Rogers wrote:
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
OK, fair enough - sorry, as I said it's been a while since I did SQL, and then it was with M$ stuff. Sorry to make you doubt yourself!
Steve
-
Chris Green -
Mark Rogers -
steve-ALUG＠hst.me.uk