2

I have time range in database:

date                   temp
2014-05-09 20:40:01    19.6875
2014-05-09 20:50:01    19.375
.....................
2014-05-10 00:10:01    17.5
........................
2014-05-23 08:25:01    27.4375

And i want get all AVG temperature with week group by hour. Here sql query:

SELECT AVG( `temp` ) , `date` FROM `temperature` WHERE `date` BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06' GROUP BY HOUR( `date` )

But in result i have only value with range from 2014-05-16 23:06:02 to 2014-05-17 00:05:01

And not have error.

Help me find my mistake.

Sory for my bad English

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
Dmitriy
  • 23
  • 2

1 Answers1

1

This is because when you use GROUP BY and display a field that isn't being grouped, mysql can't show you all the values of that field. So it just shows one.

This query makes more sense:

SELECT AVG( `temp` ) , HOUR(`date`) FROM `temperature` WHERE `date` BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06' GROUP BY HOUR( `date` )

Or this one (after discussion)

SELECT AVG( temp ) ,
    YEAR(date),
    MONTH(date),
    DAY(date),
    HOUR(date)
FROM temperature
WHERE date BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06'
GROUP BY YEAR(date),MONTH(date),DAY(date),HOUR(date)
Chris Lear
  • 6,592
  • 1
  • 18
  • 26
  • Maybe i don't understand. Test this query and i was have same result range again is from 2014-05-16 to 2014-05-17( and with 2014-05-17 to 2014-05-23 don't show( – Dmitriy May 23 '14 at 08:32
  • When you group lots of rows together to get an average, you can't know just one date that applies to that average. Specifically, if you have 3 rows that have dates on different days but the same hour (9am on consecutive days), then you query grouping by hour and ask for date in the query, mysql has no idea which of those three dates to show. So it just shows one of them. – Chris Lear May 23 '14 at 08:36
  • Well, I understand that it takes an average per hour and it's got 24 hours. Only still do not understand how I can then take a week for an hour average? – Dmitriy May 23 '14 at 11:06
  • OK, misunderstanding. I thought you wanted to group by hour of the day (ie 9am on one day same as 9am the next). But it looks like you want to group by hour for the whole week, so you get 7*24 possible records. Then use this `SELECT AVG( temp ) , YEAR(date),MONTH(date),DAY(date),HOUR(date) FROM temperature WHERE date BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06' GROUP BY YEAR(date),MONTH(date),DAY(date),HOUR(date)` – Chris Lear May 23 '14 at 11:18
  • And sorry, i don't have Vote to up your answer. When i collect first vote i immediately raise your answer – Dmitriy May 23 '14 at 12:34