汇总数据

聚集函数

聚集函数：运行在行组上，计算和返回单个值的函数

SQL聚集函数

AVG函数

例：返回products表中所有产品的平均价格：

SELECT  AVG(prod_price) AS avg_price
FROM products;

:paintbrush:AVG函数忽略列值为NULL的行

COUNT函数

确定表中行的数目或符合特定条件的行的数目。

例：返回customers表中客户的总数

SELECT COUNT(*) AS num_cust
FROM customers;

例：只对具有电子邮件的客户计数

SELECT COUNT(cust_email) AS num_cust
FROM customers;

MAX函数

SELECT MAX(prod_price) AS max_price
FROM products;

:unicorn:用于文本数据时，如果数据按相应的列排序，则max返回最后一行。

:dagger:MAX函数忽略列值为NULL的行

MIN函数

MIN函数与MAX函数正好相反

SUM函数

sum用来返回指定列值的和

例：检索所订购的物品的总数。

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

SUM还可以与之前的计算字段进行联动

例：求总的订单金额

SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;

聚集不同值

:warning:下面介绍聚集函数的DISTINCT的使用，mysql4.x是不能正常使用的。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

:date:DISTINCT不能用于COUNT（*）。

组合聚集函数

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;

分组数据

数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

例：

SELECT vend_id ,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

重要规定

1. GROUP BY子句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2. 如果在GROUP BY 子句中嵌套了分组，数据将会在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。（所以不能从个别列取回数据）
3. GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
4. 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出
5. 如果分组列中有null值，则null将作为一个分组返回，如果有多行null值，他们将分为一个组
6. GROUP BY子句必须在WHERE子句之后，ORDER BY 子句之前。

使用ROLLUP关键字

SELECT vend_id ,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;

过滤分组

HAVING非常类似WHERE。事实上，目前为止的所学习的所有类型的WHERE子句均可用HAVING来代替。唯一的区别在于HAVING过滤分组，WHERE过滤行

SELECT cust_id ,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*)>=2;

:pencil:HAVING和WHERE的区别：

WHERE是在数据分组前进行过滤，HAVING是在数据分组后进行过滤。这是一个重要区别，WHERE排除的行不包括在分组中。这可能改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

例： 列出具有2个以上，价格为10以上的产品的供应商

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

分组和排序

虽然GROUP BY和ORDER BY 经常完成相同的工作，但是他们是非常不同的。

SELECT order_num ,SUM(quantity*item_price) AS ordertotal 
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

SELECT子句顺序

SELECT 子句及其顺序