汇总数据

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

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

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