自学第十二章 汇总数据
一、聚集函数
1、AVG()函数
取平均值函数
SELECT AVG(prod_price) AS avg_price FROM products;
通过使用WHERE进行数据过滤后再取平均值
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
2、COUNT()函数
计数函数,统计表中数据行数
SELECT COUNT(*) AS num_cust FROM customers;
- NULL值 如果指定列名,则指定列的值为空的行被COUNT()函数忽略,
- 但如果COUNT()函数中用的是星号(*),则不忽略。
3、MAX() 函数 & MIN()函数
MAX()返回指定列中的最大值。
SELECT MAX(prod_price) AS max_price FROM products;
MIN()返回指定列中的最小值。
SELECT MIN(prod_price) AS max_price FROM products;
4、SUM()函数
SUM()函数返回指定列的求和总计;
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
扩展使用
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
二、聚集不同的值定DISTINCT参数
SELECT prod_price FROM products WHERE vend_id = 1003;
SELECT AVG(prod_price) FROM products WHERE vend_id = 1003;
AVG(prod_price) :取所有列的总计平均值
SELECT AVG(DISTINCT prod_price) FROM products WHERE vend_id = 1003;
AVG(DISTINCT prod_price):取剔除相同值的列求总计平均值
三、组合聚集函数
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_min, AVG(prod_price) AS price_avg FROM products;