CODEWITHSUNDEEP
mysqlsqlaverage
Chris Camacho
Chris Camacho

Reputation: 1174

mySql multiple time aggregates in query

I have managed to aggregate by time one value giving a query result like this:

SELECT date(takenat) AS takendate,
         hour(takenat) AS HourTaken, 
         avg(reading) AS ch1av 
FROM readings 
WHERE channelid = 4 
GROUP BY takendate, HourTaken 
ORDER BY takenat, HourTaken

Some sample output:

takendate HourTaken ch1av

2013-01-01 0 105.5082
2013-01-01 1 107.2167
2013-01-01 2 106.4833
2013-01-01 3 107.0333

However, I wish to have results (in the same query) for multiple channelid's

takedate HourTaken ch1av ch2av ch4av

So, for example hourly averages for channelid=1,2 and 4

I had a go with sub queries but my sql foo just aint enough!

Upvotes: 0

Views: 100

Answers (2)

Terje D.
Terje D.

Reputation: 6315

It sound that you want something like this:

SELECT DATE(takenat) AS takendate,
       HOUR(takenat) AS HourTaken,
       AVG(CASE WHEN channelid = 1 THEN reading ELSE null END) AS ch1av, 
       AVG(CASE WHEN channelid = 2 THEN reading ELSE null END) AS ch2av, 
       AVG(CASE WHEN channelid = 4 THEN reading ELSE null END) AS ch4av
FROM readings 
WHERE channelid IN (1,2,4)
GROUP BY takendate, HourTaken
ORDER BY takenat, HourTaken

Upvotes: 1

John Woo
John Woo

Reputation: 263813

use IN if you have multiple conditions,

select date(takenat) AS takendate,
       hour(takenat) AS HourTaken, 
       avg(reading) AS ch1av,
       channelid 
from   readings 
where  channelid IN (1,2,4)
group  by takendate, HourTaken, channelid 
order  by takenat, HourTaken

this is the same as using OR

select date(takenat) AS takendate,
       hour(takenat) AS HourTaken, 
       avg(reading) AS ch1av,
       channelid  
from   readings 
where  channelid = 1 OR 
       channelid = 2 OR
       channelid = 4
group  by takendate, HourTaken, channelid
order  by takenat, HourTaken

Upvotes: 2

Related Questions