[MYSQL -12]汇总数据

聚集函数：运行在行组上，计算和返回单个值的函数

函数 说明 AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值之和

select AVG(prod_price) as avg_price from products;select AVG(prod_price) as avg_price from products where vend_id=1003;select count(*) as num_cust from customers;select count(cust_email) as num_cust from customers;select max(prod_price) as max_price from products;select min(prod_price) as min_price from products;select sum(quantity) as items_ordered from orderitems where order_num=20005;select quantity ,item_price from orderitems;select sum(quantity*item_price) as total_price,sum(quantity) as quan from orderitems;-- where order_num=20005;select AVG(DISTINCT prod_price) as avg_price from products where vend_id=1003;select AVG(prod_price) as avg_price from products where vend_id=1003;select count(*) as all_count,        min(prod_price) as min_price,        max(prod_price) as max_price,        avg(prod_price) as avg_price        from products;

• AVG()函数确定特定列的均值，而且列名必须作为参数给出。多个列的均值必须使用多个AVG()函数给出。AVG()函数忽略值为NULL的行。
• COUNT(*)对表中行的数目进行计数，不管表中是空值（NULL）还是非空值。COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
• MIN()，SUM()函数忽略列值为NULL的行
• 聚集不同的值: DISTINCT

select AVG(DISTINCT prod_price) as avg_price from products where vend_id=1003; #排除了相同价格的计算。