GROUP BY分组，having 子句，在分组之后筛选数据.with rullup汇总数据

利用一列来分组 

SELECT
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id#分组，计算每个客户的总销售额

计算每个顾客下半年的销售额 

SELECT
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date > '2019-07-01'
GROUP BY client_id#分组，计算每个客户的总销售额
ORDER BY total_sales DESC

多列分组

SELECT 
	 state,
     city,
	SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING(client_id)
GROUP BY state, city#分组，计算每个客户的总销售额

 练习

 having

/*只想要总销售高于500美金的客户*/
SELECT
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
-- WHERE toatl_sales > 500因为这会还没有对数据分组，所以在第6行我们不知道每个顾客的销售额
GROUP BY client_id
HAVING total_sales > 500

 where子句在分组前筛选数据，having子句在分组后筛选数据

having中用到的列一定是select子句中存在的，换句话说我们不能使用payment列等，但是聚合函数不在select中选择，也可以跟在having后面用；

而where子句可以用任何列，不管用没有在select子句中。

where子句只能写表中存在的列，不能用起的别名

 而having可以。

where筛选原始数据，having筛选处理后的数据

SELECT 
	c.customer_id,
    c.first_name,
    c.last_name,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING(customer_id)
JOIN order_items oi USING(order_id)
WHERE state= 'va'
GROUP BY 
    c.customer_id,
    c.first_name,
    c.last_name
HAVING total_sales > 100
/*一般来讲，当选择语句中有聚合函数，而且在对数据分组，那么可以直接根据
select子句里的所有列来进行分组*/

rollup讲解

单列分组

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP
/*rollup只能用于聚合值的列*/

 

 多列分组

SELECT 
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING(client_id)
GROUP BY state, city WITH ROLLUP
/*rollup只能用于聚合值的列*/

 

roollup对每一组的总计做了计算

最后是整个结果集（所有state,city）的汇总

 小练习

SELECT 
	pm.name AS payment_method,
    SUM(p.amount) AS total
FROM payments p
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP

select 
	SUBSTRING_INDEX(profile,',',-1) AS gender,
    COUNT(SUBSTRING_INDEX(profile,',',-1))
from user_submit
group by  gender