MySQL uses aggregate functions to query a single table

Aggregate functions

Acts on a set of data and returns a value for that set of data

• count: the number of records in the statistical result.
• max: maximum value of statistics
• min: minimum statistical value
• sum: calculate the sum
• avg: Calculate the average value

Note the grouping function group by. If you want to filter the grouped data, you must use the having keyword, and the condition should be written after having;

• where: filter the existing data first, then group it, and then calculate the aggregate function;
• having: Filter the data after grouping. Insert the city table we need to practice

CREATE TABLE `city` (
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Primary key',
	`city_name` VARCHAR(100) COMMENT 'city name',
	`city_year` VARCHAR(4) COMMENT 'Year, yyyy',
	`city_gdp` DOUBLE COMMENT 'The GDP of the city for the current year, in 100 million yuan',
	`city_population` DOUBLE COMMENT 'The total population of the city in the current year of good harvest, in 10,000 people'
);

Add data

INSERT INTO city VALUES(1,'Shanghai',2018,32679,2418);
INSERT INTO city VALUES(2,'Beijing',2018,30320,2171);
INSERT INTO city VALUES(3,'深圳',2018,24691,1253);
INSERT INTO city VALUES(4,'Guangzhou',2018,23000,1450);
INSERT INTO city VALUES(5,'重庆',2018,20363,3372);
INSERT INTO city VALUES(6,'Shanghai',2019,38155,2424);
INSERT INTO city VALUES(7,'Beijing',2019,35371,2171);
INSERT INTO city VALUES(8,'深圳',2019,26927,1302);
INSERT INTO city VALUES(9,'Guangzhou',2019,23628,1491);
INSERT INTO city VALUES(10,'重庆',2019,23605,3372);

COUNT

Query and calculate how many cities have entered data in 2019 (answers 5)

SELECT COUNT(*) FROM city WHERE city_year = '2019';

MAX

Check what the highest GDP was in 2018;

SELECT MAX(city_gdp) FROM city WHERE city_year = '2018';

MIN

Find out what the lowest GDP was in 2018;

SELECT MIN(city_gdp) FROM city WHERE city_year = '2018';

SUM

Query the total GDP of all cities in 2019;

SELECT SUM(city_gdp) FROM city WHERE city_year = '2019';

AVG

Query the average GDP of all cities in 2019;

SELECT AVG(city_gdp) FROM city WHERE city_year = '2019';

For other MySQL articles, please see the following links

MySQL DDL statements

MySQL CRUD statements

MySQL Aggregate Functions

MySQL multi-table query

END…

This is the end of this article about using aggregate functions in MySQL to query a single table. For more information about using aggregate functions in MySQL to query a single table, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!