彙總函數與GROUP BY HAVING SUM()、COUNT()、AVG()、MAX()、MIN()、WITH ROLLUP、WITH CUBE

 

通常GROUP BY會搭配彙總函數使用，而常用的彙總函數有以下五個

SUM()、COUNT()、AVG()、MAX()、MIN()

 

先準備範例資料表，price為單價、amount為數量

CREATE TABLE [ORDER] 
  ( 
     no      INT NOT NULL, 
     name    [NVARCHAR](10) NOT NULL, 
     year    SMALLINT NOT NULL, 
     quarter TINYINT NOT NULL, 
     item    [NVARCHAR](10) NOT NULL, 
     price   [INT] NOT NULL, 
     amount  [INT] NOT NULL, 
     PRIMARY KEY (no) 
  ) 

go 

INSERT [ORDER] VALUES (1,'Bill',2005,1,'CPU',3000,1);
INSERT [ORDER] VALUES (2,'Bill',2005,2,'RAM',2000,4);
INSERT [ORDER] VALUES (3,'Bill',2006,4,'MB',2000,1);
INSERT [ORDER] VALUES (4,'Mary',2010,1,'SSD',2500,5);
INSERT [ORDER] VALUES (5,'Mary',2010,1,'LCD',6700,1);
INSERT [ORDER] VALUES (6,'Bryan',2015,1,'KB',500,2);
INSERT [ORDER] VALUES (7,'Bryan',2015,3,'ROUTER',1500,2);
INSERT [ORDER] VALUES (8,'Tim',2016,1,'CPU',12000,1);
INSERT [ORDER] VALUES (9,'Tim',2016,4,'MB',5000,1);

 

一、SUM()

以name為群組，計算每個人共花了多少錢

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER] 

SELECT name,Sum(price * amount) AS total 
FROM   [ORDER] 
GROUP  BY name

 

二、COUNT()

以name為群組，計算每個人共買了幾類東西

SELECT name,Count(item) 
FROM   [ORDER] 
GROUP  BY name 

SELECT name,Count(*) 
FROM   [ORDER] 
GROUP  BY name

使用COUNT()函數時，在小括弧內可以填入item、no或是資料表內的任何一個屬性，

甚至是填入萬用字元「*」，但除非有N個欄位裡有很多null值，

而不知道要COUNT那一個欄位，否則建議不要使用「*」，

因為將會造成效率降低。至於使用其他屬性時，也必須要注意到該屬性值有沒有可能是null，

因為COUNT()函數是依據該屬性值非null時再累加一，因此有null值時，

有可能會造成計數不正確，在此建議可以填入主要屬性，

因為主要鍵的屬性值不可能為null值。

 

三、AVG()

以name為群組，計算每個人平均於每件商品花了多少錢

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER] 

SELECT name,Avg(price * amount) 
FROM   [ORDER] 
GROUP  BY name

 

四、MAX()

以name為群組，列出每個人花最多錢於哪類商品而花了多少錢。

先直接看目標答案

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER]

如果使用GROUP BY name配合MAX(price*amount)時，

select_list除了被GROUP BY的欄位外，只能使用彙總函數而已，

但我想一併列出該筆的購買時間、類別都因GROUP的限制而無法列出，

應該是MAX()等之類的彙總函數，就真的只有「彙總」功能而已，

彙總完成後的結果該筆記錄基本上就跟來源沒關係了。

因此決定使用TOP 1...ORDER BY...DESC來間接完成MAX()功能。

SELECT TOP 1 no,name,year,quarter,item,price,amount,( price * amount ) total 
INTO   #table_bill_5 
FROM   [ORDER] 
WHERE  name = 'Bill' 
ORDER  BY ( price * amount ) DESC 

SELECT TOP 1 no,name,year,quarter,item,price,amount,( price * amount ) total 
INTO   #table_mary_5 
FROM   [ORDER] 
WHERE  name = 'Mary' 
ORDER  BY ( price * amount ) DESC 

SELECT TOP 1 no,name,year,quarter,item,price,amount,( price * amount ) total 
INTO   #table_bryan_5 
FROM   [ORDER] 
WHERE  name = 'Bryan' 
ORDER  BY ( price * amount ) DESC 

SELECT TOP 1 no,name,year,quarter,item,price,amount,( price * amount ) total 
INTO   #table_tim_5 
FROM   [ORDER] 
WHERE  name = 'Tim' 
ORDER  BY ( price * amount ) DESC 

SELECT no,name,year,quarter,item,price,amount,total 
FROM   #table_bill_5 
UNION 
SELECT no,name,year,quarter,item,price,amount,total 
FROM   #table_mary_5 
UNION 
SELECT no,name,year,quarter,item,price,amount,total 
FROM   #table_bryan_5 
UNION 
SELECT no,name,year,quarter,item,price,amount,total 
FROM   #table_tim_5

上例使用了暫存表又使用了UNION，還假設已知只有四個人購買，

整個Query又臭又長的...，相信還有改善的空間。

這個問題已牽扯到流程控制或子查詢範圍了，於介紹MAX()範例已足夠。

 

五、MIN()

請參考MAX()範例

 

六、HAVING

如果只想列出某個人之所有花費，則在GROUP BY之後使用HAVING，

做彙總後條件篩選。

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER]

SELECT name,Sum(price * amount) AS total 
FROM   [ORDER] 
GROUP  BY name 
HAVING name = 'Bill'

 

七、WITH ROLLUP

此功能可以列出個人花費與所有人花費，類似EXCEL的統計表。

注意，WITH ROLLUP是對此人的所有年度(name,year)組合做加總然後再加上一個總合計。

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER]

SELECT name,year,Sum(price * amount) total
FROM   [ORDER] 
GROUP  BY name,year WITH ROLLUP

 

八、WITH CUBE

與WITH ROLLUP不同的是，擁有「對此人的所有年度(name,year)組合做加總然後再加上一個總合計」之外，

再細列出個人個年度花費(如下圖綠色線條)

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER]

SELECT name,year,Sum(price*amount) total
FROM   [ORDER] 
GROUP  BY name,year WITH CUBE