GROUP BY HAVING 分组

1. 如果select里既有聚集函数，又有选择列，必然要用GROUP BY。

SELECT vend_id, COUNT(*)
FROM products

2. 只有聚集函数，可以简单select求聚集函数值

SELECT COUNT(*)
FROM products

3.GROUP BY里一定有SELELCT里除了聚集函数之外的所有列。

SELECT vend_id,products.prod_price, COUNT(*)
FROM products
GROUP BY vend_id,products.prod_price

（因为一旦 GROUP了，一个分组变成了一行，不在GROUP的列有多行，出现错误）

SELECT vend_id,products.prod_price, COUNT(*)
FROM products
GROUP BY vend_id

详解

（1）只用customers.cust_id，查询正确。 因为cust_contact依赖于cust_id，所以GROUP BY没有包含SELECT中的所有列（除聚集函数）。

SELECT
	customers.cust_contact,
	customers.cust_id,
  COUNT(orders.order_num) AS total_order
FROM
	customers
	LEFT JOIN orders
	ON orders.cust_id = customers.cust_id
GROUP BY 	customers.cust_id

（2）只用customers.cust_id，查询报错。 因为cust_id不依赖于cust_contact，所以GROUP BY没有包含SELECT中的所有列（除聚集函数）。
of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql_crash_course.customers.cust_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT列表不在GROUP BY子句中，并且包含非聚合列“mysql_crash_course.customers.cust_id”，该列在功能上不依赖于GROUP BY子句的列；这与sqlmode=only_full_group_by不兼容

SELECT
	customers.cust_contact,
	customers.cust_id,
  COUNT(orders.order_num) AS total_order
FROM
	customers
	LEFT JOIN orders
	ON orders.cust_id = customers.cust_id
GROUP BY 	customers.cust_contact

（3）推荐写上所有列

SELECT
	customers.cust_contact,
	customers.cust_id,
  COUNT(orders.order_num) AS total_order
FROM
	customers
	LEFT JOIN orders
	ON orders.cust_id = customers.cust_id
GROUP BY 	customers.cust_contact,
	customers.cust_id;

4. WHERE过滤行（分组之前），HAVING过滤分组（分组之后）。

5. WHERE里不能用聚集函数判断，HAVING可以。