/*
RiCo技術學院
微軟SQL Server 2022基礎資料庫設計和開發
   資料依欄位分群、別名或運算式分群    
*/

--單欄位分群
SELECT count(*) ,[PostalCode] 
  FROM [Person].[Address]
  group by [PostalCode]


  SELECT count([PostalCode]),[StateProvinceID]
  FROM [Person].[Address]
  group by [StateProvinceID]


--使用別名分群
  SELECT count(*)
      ,'MyPostalCode'=[PostalCode]     
  FROM [Person].[Address]
  group by MyPostalCode -- 因group by比 select 先處理
  
  --透過子查詢
  select count(*),a.MyPostalCode
  from
  (
   SELECT 'MyPostalCode'=[PostalCode] 
   FROM [Person].[Address]
  ) a
  group by a.MyPostalCode
  
  

--使用運算式分群
 SELECT 
      [City]
      ,count([ModifiedDate])    
  FROM [Person].[Address]
  group by DATEPART(month,[ModifiedDate]) ,[City]
  
SELECT 
      [City]         
  FROM [Person].[Address]
  group by [AddressLine1]+[AddressLine2],[City]
  
  

--分群並排序
  SELECT 
     DATEPART(month,[ModifiedDate]) AS N'Month'  
      ,count(*)    
  FROM [Person].[Address]
  group by DATEPART(month,[ModifiedDate])
  order by DATEPART(month,[ModifiedDate])


--搭配 HAVING
  SELECT 
     DATEPART(month,[ModifiedDate]) AS N'Month'  
      ,count(*)    
  FROM [Person].[Address]
  group by DATEPART(month,[ModifiedDate])
  HAVING DATEPART(month,[ModifiedDate]) >=8
  order by DATEPART(month,[ModifiedDate])  

  

/*作業
統計[HumanResources].[Employee]男女員工總數,排序總數由高至低
NOTE:Gender:性別欄位
*/
SELECT 'Total'=count(*)
      ,[Gender]     
  FROM [HumanResources].[Employee]
  group by [Gender]
  order by Total desc
  
  
SELECT count(*)
      ,[Gender]     
  FROM [HumanResources].[Employee]  
  group by [Gender]
  order by Total
  
