MySQL汇总数据avg、count、sum

1. 聚集函数

聚集函数

avg()函数
SQL> select avg(prod_price) as avg_price from products;
SQL> select avg(prod_price) as avg_price from products where vend_id = 1003;

count()函数
所有行计数(包括空值NULL)
SQL> select count(*) as num_cust from customers;
但一行计数(不包括空值NULL)
SQL> select count(cust_email) as num_cust from customers;

max()函数
SQL> select max(prod_price) as max_price from products;

min()函数
SQL> select min(prod_price) as min_price from products;

sum()函数
SQL> select sum(quantity) as items_ordered from orderitems where order_num = 20005;
SQL> select sum(item_price*quantity) as total_price from orderitems where order_num = 20005;

聚集不同值
SQL> select avg(distinct prod_price) as avg_price from products where vend_id = 1003;

组合聚集函数
SQL> 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;

1. 聚集函数
    avg()     返回某列的平均值
    count()   返回某列的行数
    max()     返回某列的最大值
    min()     返回某列的最小值
    sum()     返回某列值之和
2. max()函数用于文本数据时，返回排序后的最后一行的值
   min()函数用于文本数据时，返回排序后的最前一行的值
3. distinct只能用于count()，不能用于count(*)即count(distinct)