MySQL AVG() function examples

Question

I working with MySql RDBMS

mysql> SELECT VERSION();
+------------------+
| VERSION()        |
+------------------+
| 8.0.17           |
+------------------+
1 row in set

I need use MySQL AVG() function to calculate the average value of a set of values.

I have tried this query

mysql> SELECT DISTINCT
CASE WHEN xCode IS NULL THEN 'Total' ELSE xCode END AS `xCode`,
CASE WHEN xCode IS NULL THEN AVG(ROUND(IFNULL(`Average_Compilation_Time_minute`,'ND'),0))
ELSE ROUND(IFNULL(`Average_Compilation_Time_minute`,'ND'),0)
END AS `Average_Compilation_Time_minute`,
CASE WHEN xCode IS NULL THEN AVG(IFNULL(SEC_TO_TIME(MOD (`Average_Compilation_Time_minute` * 60,86400)),'ND'))
ELSE IFNULL(SEC_TO_TIME(MOD (`Average_Compilation_Time_minute` * 60,86400)),'ND')
END AS `Average_Compilation_Time_hour`,
CASE WHEN xCode IS NULL THEN AVG(IFNULL(CONCAT(FLOOR(`Average_Compilation_Time_minute` / 60),'h ',
MOD (`Average_Compilation_Time_minute`,60),'m'),'ND'))
ELSE
IFNULL(CONCAT(FLOOR(`Average_Compilation_Time_minute` / 60),'h ',
MOD (`Average_Compilation_Time_minute`,60),'m'),'ND')
END AS `Average_Compilation_Time_hour`,
sID
FROM `tbl_new_Average`
GROUP BY xCode WITH ROLLUP;
+-------+---------------------------------+-------------------------------+-------------------------------+-----+
| xCode | Average_Compilation_Time_minute | Average_Compilation_Time_hour | Average_Compilation_Time_hour | sID |
+-------+---------------------------------+-------------------------------+-------------------------------+-----+
| D1E1  |                       2848.0000 | 23:28:00                      | 47h 28m                       |   1 |
| D2E1  |                          8.0000 | 00:08:00                      | 0h 8m                         |   2 |
| D2E2  |                       3284.0000 | 06:44:00                      | 54h 44m                       |   3 |
| D4E1  |                        382.0000 | 06:22:00                      | 6h 22m                        |   4 |
| D4E2  |                        372.0000 | 06:12:00                      | 6h 12m                        |   5 |
| D5E1  |                       1481.0000 | 00:41:00                      | 24h 41m                       |   6 |
| D5E2  |                       5703.0000 | 23:03:00                      | 95h 3m                        |   7 |
| D6E1  |                        841.0000 | 14:01:00                      | 14h 1m                        |   8 |
| D6E2  |                          3.0000 | 00:03:00                      | 0h 3m                         |   9 |
| Total |                       1658.0000 | 8.666666666666666             | 27.333333333333332            |   9 |
+-------+---------------------------------+-------------------------------+-------------------------------+-----+
10 rows in set

But I have in return two different resultset on Average_Compilation_Time_hour columns

Which is the correct average in the column Average_Compilation_Time_hour columns ?

On the last row I need the AVG in this format HH:mm:ss

Please any help?

Really appreciated

My code below

DROP TABLE
IF EXISTS `tbl_new_Average`;

CREATE TABLE `tbl_new_Average` (
    `xCode` VARCHAR (255) DEFAULT NULL,
    `Average_Compilation_Time_minute` INT (11) DEFAULT NULL,
    `Average_Compilation_Time_hour` CHAR (8) DEFAULT NULL,
    `sID` INT (11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`sID`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

INSERT INTO `tbl_new_Average` VALUES('D1E1','2848','23:28:00','1');
INSERT INTO `tbl_new_Average` VALUES('D2E1','8','00:08:00','2');
INSERT INTO `tbl_new_Average` VALUES('D2E2','3284','06:44:00','3');
INSERT INTO `tbl_new_Average` VALUES('D4E1','382','06:22:00','4');
INSERT INTO `tbl_new_Average` VALUES('D4E2','372','06:12:00','5');
INSERT INTO `tbl_new_Average` VALUES('D5E1','1481','00:41:00','6');
INSERT INTO `tbl_new_Average` VALUES('D5E2','5703','23:03:00','7');
INSERT INTO `tbl_new_Average` VALUES('D6E1','841','14:01:00','8');
INSERT INTO `tbl_new_Average` VALUES('D6E2', '3', '00:03:00', '9');

update

mysql> SELECT 
COALESCE(xCode,'Total') AS xxxCode,
ROUND(`Average_Compilation_Time_minute`,0) AS `Average_Compilation_Time_minute`,
ROUND(`Average_Compilation_Time_hour2`,0) AS `Average_Compilation_Time_hour2`,
`Average_Compilation_Time_hour` AS `Average_Compilation_Time_hour`
FROM ( 

SELECT xCode,
CASE WHEN xCode IS NULL THEN AVG(IFNULL(`Average_Compilation_Time_minute`,'ND'))
ELSE IFNULL(`Average_Compilation_Time_minute`,'ND')
END AS `Average_Compilation_Time_minute`,

CASE WHEN xCode IS NULL THEN AVG(IFNULL(CONCAT(FLOOR(`Average_Compilation_Time_minute` / 60),'h ',
MOD (`Average_Compilation_Time_minute`,60),'m'),'ND'))
ELSE IFNULL(CONCAT(FLOOR(`Average_Compilation_Time_minute` / 60),'h ',
MOD (`Average_Compilation_Time_minute`,60),'m'),'ND')
END AS `Average_Compilation_Time_hour2`,

CASE WHEN xCode IS NULL THEN AVG(IFNULL(SEC_TO_TIME(MOD (`Average_Compilation_Time_minute` * 60,86400)),'ND'))
ELSE IFNULL(SEC_TO_TIME(MOD (`Average_Compilation_Time_minute` * 60,86400)),'ND')
END AS `Average_Compilation_Time_hour`

FROM `tbl_new_Average`
GROUP BY xCode WITH ROLLUP) AS q;
+---------+---------------------------------+--------------------------------+-------------------------------+
| xxxCode | Average_Compilation_Time_minute | Average_Compilation_Time_hour2 | Average_Compilation_Time_hour |
+---------+---------------------------------+--------------------------------+-------------------------------+
| D1E1    |                            2848 |                             47 | 23:28:00                      |
| D2E1    |                               8 |                              0 | 00:08:00                      |
| D2E2    |                            3284 |                             54 | 06:44:00                      |
| D4E1    |                             382 |                              6 | 06:22:00                      |
| D4E2    |                             372 |                              6 | 06:12:00                      |
| D5E1    |                            1481 |                             24 | 00:41:00                      |
| D5E2    |                            5703 |                             95 | 23:03:00                      |
| D6E1    |                             841 |                             14 | 14:01:00                      |
| D6E2    |                               3 |                              0 | 00:03:00                      |
| Total   |                            1658 |                             27 | 8.666666666666666             |
+---------+---------------------------------+--------------------------------+-------------------------------+
10 rows in set

UPDATE2

I don't understand...

Using excel the AVG is

on Mysql

23:28:00
00:08:00
06:44:00
06:22:00
06:12:00
00:41:00
23:03:00
14:01:00
00:03:00

00:18:56

In my view - You are more likely to get answers if your code can be made to fit on the least screen real estate AND it would make sense to include more than 1 row per xcode in sample data.BTW I don't get the result you get with your code. — P.Salmon, Aug 04 '20 at 14:41
asked before: https://stackoverflow.com/questions/2217139/mysql-average-on-time-column — Luuk, Aug 04 '20 at 15:16

score 0 · Accepted Answer · answered Aug 04 '20 at 17:09

0

create table tijd(t time);
insert into tijd values('23:28:00');
insert into tijd values('00:08:00');
insert into tijd values('06:44:00');
insert into tijd values('06:22:00');
insert into tijd values('06:12:00');
insert into tijd values('00:41:00');
insert into tijd values('23:03:00');
insert into tijd values('14:01:00');
insert into tijd values('00:03:00');

select sec_to_time(avg(time_to_sec(t))) from tijd;

output:

+----------------------------------+
| sec_to_time(avg(time_to_sec(t))) |
+----------------------------------+
| 08:58:00.0000                    |
+----------------------------------+

answered Aug 04 '20 at 17:09

Luuk

12,245
5
22
33

`select left(sec_to_time(avg(time_to_sec(t))),8) from tijd;` OR `select substring(sec_to_time(avg(time_to_sec(t))),1,8) from tijd;` – Luuk Aug 04 '20 at 20:27
OR `select time_format(sec_to_time(avg(time_to_sec(t))),'%H:%i:%s') from tijd;` – Luuk Aug 04 '20 at 20:33

MySQL AVG() function examples

1 Answers1