[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; #排除了相同價格的計算。