'SQL take average number of rows per minute over a period of time
I'm trying to run an SQL query on my database table. I need to calculate the average number of rows over the space of a minute based on my created_at column dating back for about 5 minutes. Meaning, if There are 10 entries in 1 minute, 20 entries in another minute then the average per minute would be 15 per minute.
When I attempt to run my query using the AVE function, I just get the following error:
Syntax error or access violation: 1305 FUNCTION database.AVE does not exist
If I remove my AVE function and simply use a COUNT then it'll give me the total number of rows for that minute, which seems like I'm very close!
SELECT
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00') AS date,
COUNT(*) as count,
AVE(count) as average
FROM tlp_queue_manager_stats
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00')
ORDER BY created_at DESC
will error.
Then this is the one that gives me a count, minus an average:
SELECT
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00') AS date,
COUNT(*) as count
FROM tlp_queue_manager_stats
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00')
ORDER BY created_at DESC
the data
array(5) {
[0]=>
array(1) {
[0]=>
array(2) {
["date"]=>
string(19) "2022-04-08 14:46:00"
["count"]=>
string(2) "32"
}
}
[1]=>
array(1) {
[0]=>
array(2) {
["date"]=>
string(19) "2022-04-08 14:45:00"
["count"]=>
string(2) "32"
}
}
[2]=>
array(1) {
[0]=>
array(2) {
["date"]=>
string(19) "2022-04-08 14:44:00"
["count"]=>
string(2) "32"
}
}
[3]=>
array(1) {
[0]=>
array(2) {
["date"]=>
string(19) "2022-04-08 14:43:00"
["count"]=>
string(2) "32"
}
}
[4]=>
array(1) {
[0]=>
array(2) {
["date"]=>
string(19) "2022-04-08 14:42:00"
["count"]=>
string(2) "32"
}
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
