MySQL数据汇总

avg()函数：返回products表中所有产品的平均价格

select avg(prod_price) as avg_price 
from products;

返回特定供应商所提供产品的平均价格

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

count()函数：返回customers表中客户的总数

select count(*) as num_cust 
from customers;

只对具有电子邮件地址的客户计数

select count(cust_email) as num_cust
from customers;

max()函数：返回products表中最贵的物品的价格

select max(prod_price) as max_price 
from products;

min()函数：返回products表中最便宜的物品的价格

select min(prod_price) as min_price 
from products;

sum()函数：检索所订购物品的总数

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

合计总订单金额

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

聚集不同值

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

组合聚集函数

select count(*) as num_items,
       min(prod_price) as price_min,
       max(prod_price) as price_max,
       avg(prod_price) as price_avg
from products;