汇总数据

聚集函数以及如何利用聚集函数汇总数据

聚集函数运行在行组上,返回单个函数值

SQL聚集函数

AVG()函数:   只能确定特定数列之的平均值,并且会忽略NULL值

select avg(prod_price) as avg_price from products;

select prod_price,prod_name,prod_id from products;

select sum(prod_price) as sum_price from products;


select prod_price,prod_name,prod_id from products where vend_id =1003;

select avg(prod_price) as avg_price from products where vend_id =1003;

select sum(prod_price) as sum_price from products where vend_id =1003;

当使用聚集函数时,不能进行筛选操作,会报错。因为聚集函数返回的时单个的函数值,而筛选操作返回的时一系列行

COUNT()函数:  计数函数

select count(*) as num_cust from customers;

select cust_name from customers;


select count(cust_email) as num_cust from customers;

select cust_email from customers;

这里对cust_email进行计数,count()函数会忽略null值,如果是count(*)则不会忽略NULL值

MAX()函数:   返回指定列最大值  忽略NULL值

MIN()函数:功能同上

select max(prod_price) as max_price from products;

select prod_price from products;

sum()函数:  求和

select sum(quantity) as items_ordered from orderitems where order_num = 20005;

select quantity from orderitems where order_num = 20005;

select item_price,quantity from orderitems where order_num = 20005;

select sum(quantity*item_price) as total_money from orderitems where order_num = 20005;

聚集不同值


select avg(distinct prod_price) as avg_price from products where vend_id = 1003;

指定供货商1003,只考虑不同价格distinct prod_price

推荐阅读更多精彩内容