Combining SUM and AVG with GROUP BY in MySQL

Question

I'm not sure if this is going to be possible in a single query, but I'm trying to achieve the following with MySQL and haven't yet found a solution.

I have a table that is structured a little like this:

 ID     LOG_VALUE   LOG_TIME

---

  1        5000     19:05:42   
  2        6205     19:05:45
  3        1000     19:05:40  
  1        5000     19:05:52    
  2        6403     19:05:55   
  3        1500     19:05:50
  1        5000     19:06:02    
  2        6403     19:06:05   
  3        1500     19:06:00

Rows are inserted into this log table at regular intervals i.e. every 10 seconds. I would like to query the records to calculate a combined average log_value for every minute.

The problem that I have is that I'm not sure how to calculate an average per ID for each minute, along with the sum of the averages for any given minute, only one or the other.

In basic terms I am wanting to achieve this process:

• Find the average of all logs for ID 1 in 19:05
• Find the average of all logs for ID 2 in 19:05
• Find the average of all logs for ID 3 in 19:05
• Add the averages together to give a total for 19:05
• Do the same for 19:06

The SQL below doesn't do what I want but I've included it to help support the problem.

SELECT ROUND(AVG(log_value)) AS average_value, EXTRACT(HOUR_MINUTE FROM log_time)
FROM (`logs`)
GROUP BY MINUTE(log_time), id
ORDER BY log_time DESC

Thanks for any help you can provide.

Answer 1

Try following

SELECT 
   ROUND(AVG(log_value)) AS average_value, 
   EXTRACT(HOUR_MINUTE FROM log_time),
   id
FROM (`logs`)
GROUP BY 
   EXTRACT(HOUR_MINUTE FROM log_time), id
ORDER BY 
   log_time DESC

Answer 2

SELECT id, 
       EXTRACT(HOUR_MINUTE FROM log_time),
       ROUND(AVG(log_value)) AS average_value
  FROM (`logs`)
 GROUP BY EXTRACT(HOUR_MINUTE FROM log_time), id
UNION ALL
SELECT null id, 
       EXTRACT(HOUR_MINUTE FROM log_time), 
       ROUND(AVG(log_value)) AS average_value
  FROM (`logs`)
 GROUP BY EXTRACT(HOUR_MINUTE FROM log_time)
ORDER BY EXTRACT(HOUR_MINUTE FROM log_time)

the first query calculate average on distinct Id, HOUR_MINUTE.
the second query calculate average on distinct HOUR_MINUTE = sum of each Id average for that HOUR_MINUTE

(id = null to do the UNION).