指定分组后,对每个分组里的数据进行聚集
-
select vend_id, count(*) as num_prods from products group by vend_id;
-
过滤分组
select cust_id, count(*) from orders group by cust_id having count(*) > =2;
注意: where是在分组前进行过滤,having是在分组后进行过滤
-
价格>=10, 具有2个产品及其以上的供应商。
select vend_id, count(*) as num_prods from products where prod_price >=10 group by vend_id having count(*) >=2;
-
总计订单价格大于等于50的订单号和总计价格
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 ordertatal