MySQL AVG() function examples

Databases
1

Hello there,

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(
		DISTINCT 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(
		DISTINCT 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(
		DISTINCT 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 | 10.75                         | 34.285714285714285            |   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 column ?

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');



10 rows in set
2

i’m going to walk you through it, a few steps at a time

you have this in your SELECT list –

CASE WHEN xCode IS NULL 
            THEN 'Total'
            ELSE xCode END AS `xCode`

here, you should be using COALESCE(xCode,'Total')

and i would also give it a different alias than xCode, but that’s just me

then you also have this –

     , CASE WHEN xCode IS NULL 
            THEN AVG( DISTINCT 
                      ROUND( 
                      IFNULL(`Average_Compilation_Time_minute`
                             , 'ND' )
                            , 0 )
                    )
            ELSE

you lost me right there

how can you perform an average on a column of values if one of those values is an alphanumeric string?

and mathematically, i’m completely discombobulated by your use of DISTINCT

what is the average of these 5 numbers – 21, 21, 21, 21, 36?

the answer is 24

now, what is the average of these distinct 5 numbers – 21, 21, 21, 21, 36?

the answer is 28.5

so that’s weird, right? but again, that could just be me

and i don’t even want to think about the difference of taking the average of some rounded numbers, versus rounding the average of those numbers

i know i haven’t really answered your question about what is the correct way to do your calculation, but the rule of thumb is – calculate your time calculations in raw seconds or minutes, whichever is the lowest you have, and then you can do your conversion to display format afterwards

and by “afterwards” i mean like this –

SELECT /* functions to convert to display format */
  FROM ( SELECT /* calculations */
           FROM ... ) AS q
1 Like
3

thanks for reply

but I went out road

the resultset is moon :laughing:

e.g.

1.658 minutes are 27,6 hours

in the resultset column Average_Compilation_Time_hour is 9 (wrong)

in the resultset column Average_Compilation_Time_hour2 is 27 (right?)

mysql> SELECT 
COALESCE(xCode,'Total') AS xxxCode,
ROUND(`Average_Compilation_Time_minute`,0) AS `Average_Compilation_Time_minute`,
FORMAT(`Average_Compilation_Time_hour`, '%H:%i:%s') AS `Average_Compilation_Time_hour`,
ROUND(`Average_Compilation_Time_hour2`,0) AS `Average_Compilation_Time_hour2`
FROM ( 
SELECT xCode,
CASE WHEN xCode IS NULL THEN AVG(ROUND(IFNULL(`Average_Compilation_Time_minute`,'ND'),0))
ELSE IFNULL(`Average_Compilation_Time_minute`,'ND')
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_hour2`
FROM `tbl_new_Average`
GROUP BY xCode WITH ROLLUP) AS q;
+---------+---------------------------------+-------------------------------+--------------------------------+
| xxxCode | Average_Compilation_Time_minute | Average_Compilation_Time_hour | Average_Compilation_Time_hour2 |
+---------+---------------------------------+-------------------------------+--------------------------------+
| D1E1    |                            2848 | 23                            |                             47 |
| D2E1    |                               8 | 0                             |                              0 |
| D2E2    |                            3284 | 6                             |                             54 |
| D4E1    |                             382 | 6                             |                              6 |
| D4E2    |                             372 | 6                             |                              6 |
| D5E1    |                            1481 | 0                             |                             24 |
| D5E2    |                            5703 | 23                            |                             95 |
| D6E1    |                             841 | 14                            |                             14 |
| D6E2    |                               3 | 0                             |                              0 |
| Total   |                            1658 | 9                             |                             27 |
+---------+---------------------------------+-------------------------------+--------------------------------+
10 rows in set
4

no, i don’t think you understood what i was saying

start with this –

SELECT *
  FROM ( SELECT xCode
              , AVG(`Average_Compilation_Time_minute`) AS avg_min  
           FROM tbl_new_Average
         GROUP 
             BY xCode WITH ROLLUP ) AS q

and then replace that asterisk with your formatting expressions, including calculating the hours, rounding, etc.

1 Like
5

So, sorry

I hope I understand

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');

SELECT
	COALESCE (xCode, 'Total') AS `avg_Code`,
	ROUND(`avg_min`, 0) AS `avg_min`,
	ROUND(`avg_hour`, 0) AS `avg_hour`,
    `avg_hour_1`
FROM
	(
		SELECT
			xCode,
			AVG(`Average_Compilation_Time_minute`) AS avg_min,
			AVG(`Average_Compilation_Time_hour`) AS avg_hour, 
            SEC_TO_TIME(AVG(TIME_TO_SEC(`Average_Compilation_Time_hour`))) AS `avg_hour_1`
		FROM
			tbl_new_Average
		GROUP BY
			xCode WITH ROLLUP
	) AS q;

+----------+---------+----------+---------------+
| avg_Code | avg_min | avg_hour | avg_hour_1    |
+----------+---------+----------+---------------+
| D1E1     | 2848    |       23 | 23:28:00.0000 |
| D2E1     | 8       |        0 | 00:08:00.0000 |
| D2E2     | 3284    |        6 | 06:44:00.0000 |
| D4E1     | 382     |        6 | 06:22:00.0000 |
| D4E2     | 372     |        6 | 06:12:00.0000 |
| D5E1     | 1481    |        0 | 00:41:00.0000 |
| D5E2     | 5703    |       23 | 23:03:00.0000 |
| D6E1     | 841     |       14 | 14:01:00.0000 |
| D6E2     | 3       |        0 | 00:03:00.0000 |
| Total    | 1658    |        9 | 08:58:00.0000 |
+----------+---------+----------+---------------+
10 rows in set
6

… are you actually doing this on a table with unique xCode values just to get a rollup row?

1 Like
7

your data looks awfully wrong

i don’t know where you got the values for your two columns Average_Compilation_Time_hour and Average_Compilation_Time_hour2 from, but they do not match the minutes

here’s the hour equivalents of your minutes –

minutes  hours 
   2848   47.5
      8    0.1
   3284   54.7
    382    6.4
    372    6.2
   1481   24.7
   5703   95.1
    841   14.0
      3    0.1
   1658   27.6

so wherever you are getting your hours data from, it’s wrong

1 Like
8

I forgot to divide the minutes by 60

this is the return

the values of last column avg_hour_1 are wrong… why?

mysql> SELECT
	COALESCE (xCode, 'Total') AS `avg_Code`,
	ROUND(`avg_min`, 0) AS `avg_min`,
	ROUND(`avg_hour`, 0) AS `avg_hour`,
    LEFT(`avg_hour_1`,8) AS `avg_hour_1`
FROM
	(
		SELECT
			xCode,
			AVG(`Average_Compilation_Time_minute`) AS avg_min,
			AVG(`Average_Compilation_Time_minute` / 60) AS avg_hour, 
            SEC_TO_TIME(AVG(TIME_TO_SEC(`Average_Compilation_Time_hour`))) AS `avg_hour_1`
		FROM
			tbl_new_Average
		GROUP BY
			xCode WITH ROLLUP
	) AS q;
+----------+---------+----------+------------+
| avg_Code | avg_min | avg_hour | avg_hour_1 |
+----------+---------+----------+------------+
| D1E1     | 2848    | 47       | 23:28:00   |
| D2E1     | 8       | 0        | 00:08:00   |
| D2E2     | 3284    | 55       | 06:44:00   |
| D4E1     | 382     | 6        | 06:22:00   |
| D4E2     | 372     | 6        | 06:12:00   |
| D5E1     | 1481    | 25       | 00:41:00   |
| D5E2     | 5703    | 95       | 23:03:00   |
| D6E1     | 841     | 14       | 14:01:00   |
| D6E2     | 3       | 0        | 00:03:00   |
| Total    | 1658    | 28       | 08:58:00   |
+----------+---------+----------+------------+
10 rows in set
9

because the data is bad

1 Like
10

Solved

thanks for this help

mysql> SELECT
	COALESCE (xCode, 'Total') AS `avg_Code`,
	ROUND(`avg_min`, 0) AS `avg_min`,
	ROUND(`avg_hour`, 0) AS `avg_hour`,
    LEFT(`avg_hour_1`,8) AS `avg_hour_1`
FROM
	(
		SELECT
			xCode,
			AVG(`Average_Compilation_Time_minute`) AS avg_min,
			AVG(`Average_Compilation_Time_minute` / 60) AS avg_hour, 
            SEC_TO_TIME(AVG(`Average_Compilation_Time_minute`)*60) AS `avg_hour_1`
		FROM
			tbl_new_Average
		GROUP BY
			xCode WITH ROLLUP
	) AS q;
+----------+---------+----------+------------+
| avg_Code | avg_min | avg_hour | avg_hour_1 |
+----------+---------+----------+------------+
| D1E1     | 2848    | 47       | 47:28:00   |
| D2E1     | 8       | 0        | 00:08:00   |
| D2E2     | 3284    | 55       | 54:44:00   |
| D4E1     | 382     | 6        | 06:22:00   |
| D4E2     | 372     | 6        | 06:12:00   |
| D5E1     | 1481    | 25       | 24:41:00   |
| D5E2     | 5703    | 95       | 95:03:00   |
| D6E1     | 841     | 14       | 14:01:00   |
| D6E2     | 3       | 0        | 00:03:00   |
| Total    | 1658    | 28       | 27:38:00   |
+----------+---------+----------+------------+
10 rows in set
11

looks okay (well, except for the numbers on the Total line)

i still advise calculating only the minutes average inside the q subquery – hourly calculations should be done in the outer query

1 Like
12

do you mean this?

mysql> SELECT
	COALESCE (xCode, 'Total') AS `avg_Code`,
	ROUND(`avg_min`, 0) AS `avg_min`,
	ROUND(`avg_hour`/60, 1) AS `avg_hour`,
    LEFT(SEC_TO_TIME(`avg_hour_1`*60),8)  AS `avg_hour_1`
FROM
	(
		SELECT
			xCode,
			AVG(`Average_Compilation_Time_minute`) AS `avg_min`,
			AVG(`Average_Compilation_Time_minute`) AS `avg_hour`, 
            AVG(`Average_Compilation_Time_minute`) AS `avg_hour_1`
		FROM
			tbl_new_Average
		GROUP BY
			xCode WITH ROLLUP
	) AS q;
+----------+---------+----------+------------+
| avg_Code | avg_min | avg_hour | avg_hour_1 |
+----------+---------+----------+------------+
| D1E1     | 2848    | 47,5     | 47:28:00   |
| D2E1     | 8       | 0,1      | 00:08:00   |
| D2E2     | 3284    | 54,7     | 54:44:00   |
| D4E1     | 382     | 6,4      | 06:22:00   |
| D4E2     | 372     | 6,2      | 06:12:00   |
| D5E1     | 1481    | 24,7     | 24:41:00   |
| D5E2     | 5703    | 95,1     | 95:03:00   |
| D6E1     | 841     | 14       | 14:01:00   |
| D6E2     | 3       | 0,1      | 00:03:00   |
| Total    | 1658    | 27,6     | 27:38:00   |
+----------+---------+----------+------------+
10 rows in set
13

no!

please see post #4

1 Like
14

Okay I see

thanks

mysql> SELECT
	COALESCE (xCode, 'Total') AS `avg_Code`,
	ROUND(`avg_min`, 0) AS `avg_min`,
	ROUND(`avg_min`/60, 1) AS `avg_hour`,
    LEFT(SEC_TO_TIME(`avg_min`*60),8)  AS `avg_hour_1`
FROM
	(
		SELECT
			xCode,
            AVG(`Average_Compilation_Time_minute`) AS avg_min  
		FROM
			tbl_new_Average
		GROUP BY
			xCode WITH ROLLUP
	) AS q;
+----------+---------+----------+------------+
| avg_Code | avg_min | avg_hour | avg_hour_1 |
+----------+---------+----------+------------+
| D1E1     | 2848    | 47,5     | 47:28:00   |
| D2E1     | 8       | 0,1      | 00:08:00   |
| D2E2     | 3284    | 54,7     | 54:44:00   |
| D4E1     | 382     | 6,4      | 06:22:00   |
| D4E2     | 372     | 6,2      | 06:12:00   |
| D5E1     | 1481    | 24,7     | 24:41:00   |
| D5E2     | 5703    | 95,1     | 95:03:00   |
| D6E1     | 841     | 14       | 14:01:00   |
| D6E2     | 3       | 0,1      | 00:03:00   |
| Total    | 1658    | 27,6     | 27:38:00   |
+----------+---------+----------+------------+
10 rows in set
16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.