Explanation of MySQL Query Operation Example Using Aggregate Function

In this paper, the example tells us that MySQL uses aggregate function for query operation. Share it for your reference, as follows:

COUNT Function

SELECT COUNT(*) AS cust_num from customers;
SELECT COUNT(c_email) AS email_num FROM customers;
SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num;

SUM Function

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

AVG Function

SELECT AVG(f_price) AS avg_price FROM fruits WHERE s_id = 103;
SELECT AVG(f_price) AS avg_price FROM fruits group by s_id;

MAX Function

SELECT MAX(f_price) AS max_price FROM fruits;
SELECT s_id, MAX(f_price) AS max_price FROM fruits GROUP BY s_id;
SELECT MAX(f_name) from fruits;

MIN Function

SELECT MIN(f_price) AS min_price FROM fruits;
SELECT s_id, MIN(f_price) AS min_price FROM fruits GROUP BY s_id;

"Example. 34" Query the total number of rows in the customers table

SELECT COUNT(*) AS cust_num from customers;

[Example. 35] To query the total number of customers with e-mail addresses in the customers table, enter the following statement:

SELECT COUNT(c_email) AS email_num
FROM customers;

"Example. 36" In the orderitems table, use the COUNT() The function counts the types of fruits ordered in different order numbers

SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num;

[Example. 37] Query the total amount of fruit purchased in Order 1 No.30005 in the orderitems table, and enter the following statement:

SELECT SUM(quantity) AS items_total
FROM orderitems
WHERE o_num = 30005;

"Example. 38" In the orderitems table, use the SUM() Function to count the total amount of fruit ordered in different order numbers

SELECT o_num, SUM(quantity) AS items_total
FROM orderitems
GROUP BY o_num;

[Example. 39] In the fruits table, query the average value of fruit prices for suppliers with s_id=103. The SQL statement is as follows:

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

0

[Example. 40] In the fruits table, query the average fruit price per 1 supplier. The SQL statement is as follows:

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

1

[Example. 41] Look for the most expensive fruit on the market in the fruits table. The SQL statement is as follows:

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

2

[Example 7.42] Find the fruit with the highest price provided by different suppliers in fruits table

SELECT s_id, MAX(f_price) AS max_price
FROM fruits
GROUP BY s_id;

[Example. 43] Find the maximum value of f_name in the fruits table. The SQL statement is as follows

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

4

[Example. 44] Look for the lowest price fruit on the market in the fruits table. The SQL statement is as follows:

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

5

"Example. 45" Find the fruit with the lowest price provided by different suppliers in fruits table

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

6

More readers interested in MySQL can check out the topics on this site: "MySQL Common Functions Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.