The Group By Clause | Group By子句 | MySQL

Group By

单列分组

上节学习了如何使用聚合函数汇总数据
当前我们面对的情景：已知总的销售额，但想了解每个客户的总销售是多少
这个时候就需要对一列或多列进行数据分组

SELECT
	SUM(invoice_total) AS total_sales
FROM invoices

例如按照客户id来分列，我们可以在FROM下面增加GROUP BY client_id 但是这样的查询返回我们看不到客户具体是谁

因此需要在select语句中增加我们想要看到的返回值

SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id

除此之外可以增加一些其它的用法
例如：

• 将销售额降序排列ORDER BY Operator | MySQL
  

SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC

• 进一步思考，假设你相对每个客户计算总销售额，但仅仅计算下半年的
  并不难对不~增加一下WHERE条件句

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
  FROM
  WHERE
  GROUP BY
  ORDER BY
  顺序是既定的，否则会出现报错

多列分组

查看每个州和城市组合的总销售量

SELECT 
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id)
GROUP BY state,city -- 如果state重复，则再用city判断先后顺序

练习

请查询得到下属结果

SELECT 
	date,
    pm.name AS payment_method,
    SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm 
	ON p.payment_method = pm.payment_method_id
GROUP by date,payment_method
ORDER BY date

这里有一个小疑问，为什么一定需要GROUP BY？
如果删掉GROUP BY并执行上述代码，会报错

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sql_invoicing.p.date'; this is incompatible with sql_mode=only_full_group_by

从表格输出理解层面大概理解了，如果没有GROUP BY的话，第三列没法输出