SQL汇总数据

//AVG函数，求某列或某列特定的平均值
SELECT AVG(prod_price) AS avg_price
FROM Products
//进行筛选使用AVG
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id ='DLL01'
//COUNT函数确定行数或特定行数
SELECT COUNT (*)  AS numcust
SELECT COUNT(cust_email) AS num_cust//只筛选有电子邮件的行
FROM Customers 
//MAX函数返回列中的最大值，MIN函数返回列中的最小值
//如果用于文本数据，将返回排序后的最大值和最小值（最后一行或第一行）
SELECT MAX(prod_price) AS max_price//返回prod_price列的最大值
SELECT MIN(prod_price) AS max_price//返回prod_price列的最小值
FROM Products
//SUM函数返回指定列值的总和
SELECT SUM(quantity) AS items_ordered//返回quantity列的和
SELECT SUM(quantity*item_price)  AS total_price//在两个列上进行计算
FROM OrderItems
WHERE order_num=20005//筛选特定行
//使用DISTINCT结合聚集函数
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id='DLL01'
//组合聚集函数
SELECT COUNT(*) AS num_items
             MIN(prod_price) AS price_min
             MAX(prod_price) AS price_max
             AVG(prod_price) AS price_avg
FROM Products