SQL資料的分組與彙總

//資料的分組與彙總
GROUP BY 用於根據前面的的參數集分組結果
SELECT _State
FORM MemberDetails
GROUP BY _State;

SELECT _State
FROM MemberDetails
WHERE _State IN ('Mega state','Golden State','new State')
GROUP BY _State;

//可以基於多列分組
SELECT _State
FROM MemberDetails
WHERE _State IN ('Mega state','Golden State','new State')
GROUP BY _State,City;

//資料匯總彙總函式
COUNT()    //用於統計結果中記錄的數目,統計非NULL值,可以接受運算式作為參數,可以插入萬用字元*

SELECT COUNT(*)
FROM MemberDetails;

SELECT COUNT(City),COUNT(LastName)
FROM MemberDetails;

//根據SQL規則,不允許組合彙總函式和非彙總的列
SELECT City,COUNT(MemberId)
FROM MemberDetails;
//這樣是不行的,因為City可能返回多行資料,而COUNT僅返回一行

SELECT _State,COUNT(LastName)
FROM MemberDetails
GROUP BY _State;
//這樣就可以了 

//Group by 實質上將結果分為多個組,每個組都是所有記錄的一個子集

SUM() //用於累加結果
//自動忽略NULL值

AVG()  //用於求結果平均值

MAX()  //求最大值
MIN()  //求最小值

//HAVING子句用於過濾最終結果中的分組.HAVING子句類似於一個WHERE子句,它的位置緊緊跟在GROUP BY子句之後

SELECT Category,COUNT(FavCategory.CategoryId) AS Popularity
FROM FavCategory INNER JOIN Category
ON FavCategory.CategoryId = Category.CategoryId
GROUP BY Category.Category
HAVING COUNT(FavCategory.CategoryId) > 3
ORDER BY Popularity DESC;