SQL question for the SQL gurus out there
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. -- Chris Green
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) -- Mark Rogers // More Solutions Ltd (Peterborough Office) // 0844 251 1450 Registered in England (0456 0902) @ 13 Clarke Rd, Milton Keynes, MK1 1LG
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. -- Chris Green
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 -- Mark Rogers // More Solutions Ltd (Peterborough Office) // 0844 251 1450 Registered in England (0456 0902) @ 13 Clarke Rd, Milton Keynes, MK1 1LG
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. -- Chris Green
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
participants (3)
-
Chris Green -
Mark Rogers -
steve-ALUG＠hst.me.uk