Myujth

Having this simple log table

id |val |dt
-------------------------
 A 100 2014-01-15 00:00:00 
 A 160 2014-01-15 00:00:00 
 A 100 2014-01-15 01:00:00 
 A 160 2014-01-15 02:00:00
 A 200 2014-01-15 03:00:00
 A 80 2014-01-16 01:00:00
 B 100 2014-01-16 02:00:00
 B 200 2014-01-16 01:00:00
 B 100 2014-01-15 02:00:00
 and so on...

I can average a SINGLE day(15), of a SINGLE given id (A), in a specified range (0-2) by doing this

select id, 
 date(dt),
 AVG(val) as av
from (SELECT id, val, dt
 FROM test
 WHERE id = 'A' AND 
 (date(dt) BETWEEN '2014-01-15' AND '2014-01-15') AND 
 ( (time(dt) BETWEEN '00:00' AND '02:00'))) as outerTable

I get this SINGLE result

id |date | average 00:00 to 2:00
-------------------------
A 2014-01-15 120.0000

But how to solve that average of MULTIPLE days in same time period, but in many days in range of months? years? like

id |date | average 00:00 to 2:00
-------------------------
A 2014-01-15 120.0000
A 2014-01-16 80.0000 
A 2014-01-17 35.0000
and so on...

Need of loop the same query on different days for the whole month or year, of course the literal loop can be done in php, but it is SLOW, Also I could loop for every ID, making it slower.

You do not need to use Subquery, to get the data and then calculate the average in the Outer query. It can be done without the subquery itself.

In order to get the average value for different dates, you can remove the WHERE condition on date, to get the data for all the date(s). You can also change the WHERE condition on date to include a range instead.

You also seem to want results for different id values. We can get rid of WHERE condition on id as well.

Eventually, you can do a GROUP BY on the id and date, to get individual rows idwise, and then datewise.

SELECT 
 id, 
 DATE(dt)
 AVG(val) 
FROM test
WHERE 
 TIME(dt) BETWEEN '00:00' AND '02:00'
GROUP BY id, DATE(dt)