Mysql的聚合函数的详细使用方法

Mysql的聚合函数的详细使用方法

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    salesperson VARCHAR(50),
    product VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, salesperson, product, amount) VALUES
('2023-01-01', 'Alice', 'Product A', 100.00),
('2023-01-01', 'Bob', 'Product B', 150.00),
('2023-01-02', 'Alice', 'Product A', 200.00),
('2023-01-02', 'Charlie', 'Product C', 300.00),
('2023-01-03', 'Bob', 'Product B', 250.00),
('2023-01-03', 'Charlie', 'Product A', 100.00),
('2023-01-04', 'Alice', 'Product C', 120.00),
('2023-01-04', 'Bob', 'Product A', 80.00);

#COUNT() 函数用于统计表中行的数量，或者特定列中非NULL值的数量。
SELECT COUNT(*) AS total_sales FROM sales;
#
SELECT salesperson, COUNT(*) AS sales_count FROM sales GROUP BY salesperson;

#SUM() 函数用于计算数值列的总和。
SELECT SUM(amount) AS total_amount FROM sales;
#
SELECT salesperson, SUM(amount) AS total_sales_amount FROM sales GROUP BY salesperson;

#AVG() 函数用于计算数值列的平均值。
SELECT AVG(amount) AS average_amount FROM sales;
#
SELECT salesperson, AVG(amount) AS average_sales_amount FROM sales GROUP BY salesperson;

#MAX() 和 MIN() 函数分别用于查找数值列的最大值和最小值。
SELECT MAX(amount) AS max_amount FROM sales;
SELECT MIN(amount) AS min_amount FROM sales;
#
SELECT salesperson, MAX(amount) AS max_sales_amount, MIN(amount) AS min_sales_amount FROM sales GROUP BY salesperson;

#GROUP_CONCAT() 函数用于将分组中的多个值连接成一个字符串，通常用于字符串类型的列。
SELECT salesperson, GROUP_CONCAT(product SEPARATOR ', ') AS products_sold FROM sales GROUP BY salesperson;