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;