Description:
the results should be the same ,but after changing AVG() to SUM()/COUNT(),the first column's value become NULL
mysql> (SELECT AVG(dvr73.id) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85;
+-----------+------------+-------------------+-------------------+
| col_1 | col_2 | col_3 | col_4 |
+-----------+------------+-------------------+-------------------+
| 7588.0000 | 2025-07-07 | 1950901515@qq.com | 1950901515@qq.com |
| 8253.0000 | 2024-11-14 | 3785152080@qq.com | 3785152080@qq.com |
| 9052.0000 | 2025-02-07 | 3294990367@qq.com | 3294990367@qq.com |
| 3485.0000 | 2025-09-22 | 5883334870@qq.com | 5883334870@qq.com |
| 1172.0000 | 2024-10-14 | 1484604155@qq.com | 1484604155@qq.com |
| 1172.0000 | 2024-10-14 | girl | sample_590 |
| 2170.0000 | 2025-05-12 | boy | sample_318 |
| 3415.0000 | 2025-04-27 | girl | sample_434 |
| 3485.0000 | 2025-09-22 | girl | sample_615 |
| 6219.0000 | 2024-12-01 | girl | sample_453 |
| 7588.0000 | 2025-07-07 | boy | sample_970 |
| 8253.0000 | 2024-11-14 | girl | sample_766 |
| 8492.0000 | 2024-11-12 | girl | sample_128 |
| 9052.0000 | 2025-02-07 | boy | sample_722 |
| 9722.0000 | 2025-08-07 | girl | sample_312 |
+-----------+------------+-------------------+-------------------+
15 rows in set (0.003 sec)
mysql> (SELECT (SUM(dvr73.id) / COUNT(dvr73.id)) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85;
+-----------+------------+-------------------+-------------------+
| col_1 | col_2 | col_3 | col_4 |
+-----------+------------+-------------------+-------------------+
| NULL | 2025-07-07 | 1950901515@qq.com | 1950901515@qq.com |
| NULL | 2024-11-14 | 3785152080@qq.com | 3785152080@qq.com |
| NULL | 2025-02-07 | 3294990367@qq.com | 3294990367@qq.com |
| NULL | 2025-09-22 | 5883334870@qq.com | 5883334870@qq.com |
| NULL | 2024-10-14 | 1484604155@qq.com | 1484604155@qq.com |
| 1172.0000 | 2024-10-14 | girl | sample_590 |
| 2170.0000 | 2025-05-12 | boy | sample_318 |
| 3415.0000 | 2025-04-27 | girl | sample_434 |
| 3485.0000 | 2025-09-22 | girl | sample_615 |
| 6219.0000 | 2024-12-01 | girl | sample_453 |
| 7588.0000 | 2025-07-07 | boy | sample_970 |
| 8253.0000 | 2024-11-14 | girl | sample_766 |
| 8492.0000 | 2024-11-12 | girl | sample_128 |
| 9052.0000 | 2025-02-07 | boy | sample_722 |
| 9722.0000 | 2025-08-07 | girl | sample_312 |
+-----------+------------+-------------------+-------------------+
15 rows in set (0.002 sec)
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NULL,
age INT NULL,
created_at DATE NOT NULL,
sex VARCHAR(10) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7588, 'sample_970', '1950901515@qq.com', 71, '2025-07-07', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2170, 'sample_318', '3858244564@qq.com', 34, '2025-05-12', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (6219, 'sample_453', '8089211259@qq.com', 83, '2024-12-01', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8492, 'sample_128', '3366242209@qq.com', 22, '2024-11-12', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1172, 'sample_590', '1484604155@qq.com', 50, '2024-10-14', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3415, 'sample_434', '2281411193@qq.com', 6, '2025-04-27', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9722, 'sample_312', '7012963222@qq.com', 87, '2025-08-07', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9052, 'sample_722', '3294990367@qq.com', 55, '2025-02-07', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8253, 'sample_766', '3785152080@qq.com', 91, '2024-11-14', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3485, 'sample_615', '5883334870@qq.com', 28, '2025-09-22', 'girl');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3530, 1172, 37, 'to_finish', '2025-08-03');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9518, 9052, 770, 'finished', '2025-01-19');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3246, 7588, 782, 'to_finish', '2025-09-17');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3088, 3485, 63, 'to_finish', '2025-09-13');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9904, 9722, 166, 'finished', '2025-01-30');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6421, 3485, 902, 'finishing', '2025-09-19');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8856, 9722, 309, 'finishing', '2025-07-24');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1178, 9052, 64, 'finishing', '2025-09-04');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (27, 2170, 626, 'finishing', '2025-04-02');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7965, 3415, 562, 'finishing', '2025-09-19');
(SELECT AVG(dvr73.id) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85;
(SELECT (SUM(dvr73.id) / COUNT(dvr73.id)) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85;
Description: the results should be the same ,but after changing AVG() to SUM()/COUNT(),the first column's value become NULL mysql> (SELECT AVG(dvr73.id) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85; +-----------+------------+-------------------+-------------------+ | col_1 | col_2 | col_3 | col_4 | +-----------+------------+-------------------+-------------------+ | 7588.0000 | 2025-07-07 | 1950901515@qq.com | 1950901515@qq.com | | 8253.0000 | 2024-11-14 | 3785152080@qq.com | 3785152080@qq.com | | 9052.0000 | 2025-02-07 | 3294990367@qq.com | 3294990367@qq.com | | 3485.0000 | 2025-09-22 | 5883334870@qq.com | 5883334870@qq.com | | 1172.0000 | 2024-10-14 | 1484604155@qq.com | 1484604155@qq.com | | 1172.0000 | 2024-10-14 | girl | sample_590 | | 2170.0000 | 2025-05-12 | boy | sample_318 | | 3415.0000 | 2025-04-27 | girl | sample_434 | | 3485.0000 | 2025-09-22 | girl | sample_615 | | 6219.0000 | 2024-12-01 | girl | sample_453 | | 7588.0000 | 2025-07-07 | boy | sample_970 | | 8253.0000 | 2024-11-14 | girl | sample_766 | | 8492.0000 | 2024-11-12 | girl | sample_128 | | 9052.0000 | 2025-02-07 | boy | sample_722 | | 9722.0000 | 2025-08-07 | girl | sample_312 | +-----------+------------+-------------------+-------------------+ 15 rows in set (0.003 sec) mysql> (SELECT (SUM(dvr73.id) / COUNT(dvr73.id)) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85; +-----------+------------+-------------------+-------------------+ | col_1 | col_2 | col_3 | col_4 | +-----------+------------+-------------------+-------------------+ | NULL | 2025-07-07 | 1950901515@qq.com | 1950901515@qq.com | | NULL | 2024-11-14 | 3785152080@qq.com | 3785152080@qq.com | | NULL | 2025-02-07 | 3294990367@qq.com | 3294990367@qq.com | | NULL | 2025-09-22 | 5883334870@qq.com | 5883334870@qq.com | | NULL | 2024-10-14 | 1484604155@qq.com | 1484604155@qq.com | | 1172.0000 | 2024-10-14 | girl | sample_590 | | 2170.0000 | 2025-05-12 | boy | sample_318 | | 3415.0000 | 2025-04-27 | girl | sample_434 | | 3485.0000 | 2025-09-22 | girl | sample_615 | | 6219.0000 | 2024-12-01 | girl | sample_453 | | 7588.0000 | 2025-07-07 | boy | sample_970 | | 8253.0000 | 2024-11-14 | girl | sample_766 | | 8492.0000 | 2024-11-12 | girl | sample_128 | | 9052.0000 | 2025-02-07 | boy | sample_722 | | 9722.0000 | 2025-08-07 | girl | sample_312 | +-----------+------------+-------------------+-------------------+ 15 rows in set (0.002 sec) How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255) NULL, age INT NULL, created_at DATE NOT NULL, sex VARCHAR(10) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(50) NOT NULL, order_date DATE NOT NULL, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id) ); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7588, 'sample_970', '1950901515@qq.com', 71, '2025-07-07', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2170, 'sample_318', '3858244564@qq.com', 34, '2025-05-12', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (6219, 'sample_453', '8089211259@qq.com', 83, '2024-12-01', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8492, 'sample_128', '3366242209@qq.com', 22, '2024-11-12', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1172, 'sample_590', '1484604155@qq.com', 50, '2024-10-14', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3415, 'sample_434', '2281411193@qq.com', 6, '2025-04-27', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9722, 'sample_312', '7012963222@qq.com', 87, '2025-08-07', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9052, 'sample_722', '3294990367@qq.com', 55, '2025-02-07', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8253, 'sample_766', '3785152080@qq.com', 91, '2024-11-14', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3485, 'sample_615', '5883334870@qq.com', 28, '2025-09-22', 'girl'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3530, 1172, 37, 'to_finish', '2025-08-03'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9518, 9052, 770, 'finished', '2025-01-19'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3246, 7588, 782, 'to_finish', '2025-09-17'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3088, 3485, 63, 'to_finish', '2025-09-13'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9904, 9722, 166, 'finished', '2025-01-30'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6421, 3485, 902, 'finishing', '2025-09-19'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8856, 9722, 309, 'finishing', '2025-07-24'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1178, 9052, 64, 'finishing', '2025-09-04'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (27, 2170, 626, 'finishing', '2025-04-02'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7965, 3415, 562, 'finishing', '2025-09-19'); (SELECT AVG(dvr73.id) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85; (SELECT (SUM(dvr73.id) / COUNT(dvr73.id)) AS col_1, dvr73.created_at AS col_2, dvr73.email AS col_3, dvr73.email AS col_4 FROM users AS dvr73 GROUP BY dvr73.created_at, dvr73.name, dvr73.email, dvr73.name, dvr73.email, dvr73.name ORDER BY dvr73.name DESC LIMIT 5) UNION ALL SELECT foi85.id AS col_1, foi85.created_at AS col_2, foi85.sex AS col_3, foi85.name AS col_4 FROM users AS foi85;