mysql汇总数据_MySQL汇总数据

1. 聚集函数

聚集函数

avg()函数

select avg(prod_price) as avg_price from products;

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

count()函数

所有行计数(包括空值NULL)

select count(*) as num_cust from customers;

但一行计数(不包括空值NULL)

select count(cust_email) as num_cust from customers;

max()函数

select max(prod_price) as max_price from products;

min()函数

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;

1. 聚集函数

avg() 返回某列的平均值

count() 返回某列的行数

max() 返回某列的最大值

min() 返回某列的最小值

sum() 返回某列值之和

2. max()函数用于文本数据时，返回排序后的最后一行的值

min()函数用于文本数据时，返回排序后的最前一行的值

3. distinct只能用于count()，不能用于count(*)即count(distinct)