MySQL query question

kapo · Post by **kapo** » Sat 27 Dec 2014 6:39 pm

Hi
I have db Monthlylog and there table "Monthly". I can make queries as: $query = "SELECT LogDateTime, max(Temp) as maxTemp, max(Dewpoint) as maxDewpoint, FROM Monthly WHERE LogDateTime > '2012-12-18 18:20:00'";
So I can make queries as min, max, avg etc. But how can I get timestamps/dates to those vals, for example: Max. Temp 30,9C @ 2014-06-07? So would someone be so friendly and give me some advice in this? As how to?

regards: Kapo

mcrossley · Post by **mcrossley** » Sat 27 Dec 2014 8:02 pm

You need to 'group by' the interval of interest. The daily min/max/avg temp are already done for you of course in the daily file/table.

kapo · Post by **kapo** » Sun 28 Dec 2014 5:42 am

Thank You about your answer. Yes I know those other daily max, min... values. And there are also all kind of max, min, avg values too inside my pages... But the thing I ask this is that I want to learn more about MySQL use... My favorit pages are W3Schools online Web Tutorials, and from there I have learnd some little things. Is it possible to have a little example of that 'to group by' query? If not, that's okay, I understand that too.

regards Kapo

mcrossley · Post by **mcrossley** » Mon 29 Dec 2014 12:00 am

Sorry reading your first post again it sees you are just after the max or min or avg of some values over a range of dates? If that is the case, then if you also want the times of each one then you just have to also select the logdatetime as you have done. The gotcha is that if you want the date/time then you will have to specify a single value for each query. The aggregate functions Max min etc only return a single row, the date time for each will be different, so you have to put them in separate queries. (It may be possible to do it in a single query using some fancy joins and or sub-queries, but the base query should be really fast, just run it a number of times. My top 10s page runs 20-30 queries in sequence in under a second on my old 'puter)

Karv · Post by **Karv** » Mon 29 Dec 2014 12:33 pm

something like :
$query = "SELECT b.LogDateTime,max(a.Temp) as maxTemp, max(a.Dewpoint) as maxDewpoint FROM Monthly a right join Monthly b using (LogDateTime) WHERE a.LogDateTime > '2012-12-18 18:20:00'";

I don't have this table at hand and am at work, so this is very theoretical but will hopefully provide insight into how to join a table to itself to get "both" results in one go.

Cheers
K

mcrossley · Post by **mcrossley** » Mon 29 Dec 2014 1:17 pm

Not quite, because in that example you need to to select logdatetime twice, once for the occurrence of max(temp) and again for the occurrence of max(dewpoint).

Karv · Post by **Karv** » Mon 29 Dec 2014 1:21 pm

yes, but then LogDateTime probably isn't even the best thing to use as the join either.

If someone can give me 30 or so lines from their table (ideally a dump file) I'll set it up here and have a play as its so quiet.

I'd probably opt for individual max / min queries to remove any ambiguity anyway, or a daily script to set the values for the month or something.

kapo · Post by **kapo** » Mon 29 Dec 2014 3:21 pm

Thank You
I got what I wanted.

regards: Kapo

Cumulus Support

MySQL query question

MySQL query question

Re: MySQL query question

Re: MySQL query question

Re: MySQL query question

Re: MySQL query question

Re: MySQL query question

Re: MySQL query question

Re: MySQL query question