mysql语句之汇总语句和分组数据(GROUP BY 和 HAVING)

1.取prod_price列的平均值输出

SELECT AVG(prod_price) AS avg_price
FROM table_name;

2.取prod_price列中的不同值计算平均值输出

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM table_name;

3.组合使用聚集函数，输出四个相应的计算后的值。

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM table_name;

4.GROUP BY子句指示mysql按vend_id分组数据。

SELECT vend_id,COUNT(*) AS num_prods
FROM vendors
GROUP BY vend_id;

5.HAVING 起着过滤分组的作用(在以分组的基础上进行过滤)

SELECT cust_id,COUNT(*) AS orders
FROM table_name
GROUP BY cust_id
HAVING COUNT(*)>=2;

6.如果我们想将分组后的结果进行排序我们可以在后面添加order by子句：

SELECT vend_id,COUNT(*) AS num_prods
FROM table_name
GROUP BY vend_id
ORDER BY num_prods;