SQL分组数据

数据分组

select vend_id ， COUNT(*) AS  num_prods
from products
GROUP BY  vend_id;

注意
1：GROUP BY子句可以包含任意数目的列。
2：如果在GROUP BY子句嵌套了分组，数据将在最后规定的分组上进行分组
3：GROUP BY 自居中列出的每个列都必须是检索列或有效地表达式
4：除聚集计算语句外，select语句中的每个列都必须在GROUP BY子句中给出
5：如果分组列中具有NULL值，将NULL将作为一个分组返回。
6：GROUP BY子句必须出现在where子句之后，order by子句之前。

过滤分组

select cust_id ,  COUNT(*) AS orders
from orders
GROUP BY cust_id
HAVING COUNT(*)  >=2;

分析
having能使用where所能使用，having在数据分组后进行过滤，where在分组前进行过滤

列出含有2个以上，价格10以上的产品的商品

select vend_id ,COUNT(*) AS num_prods
from products
where prod_price >=10
GROUP　BY vend_id
HAVING COUNT(*) >=2;

分组与排序

select order_num, SUM(quantity*item_price) AS ordertotal
from orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >=50;

select order_num, SUM(quantity*item_price) AS ordertotal
from orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >=50;
order by ordertotal;