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;