SQL Serverで日/週/月/四半期/年別 集計(sql)

日別以外はDATEPARTで処理するので実質2パターンです。

日別

SELECT 
	count(*) as cnt,
	CONVERT(date, T.Date) as Date 
FROM TestTable AS T
GROUP BY CONVERT(date, T.Date)

週別

SELECT 
	count(*) as cnt,
	DATEPART(Year, W.Date) as Year, DATEPART(WEEK, W.Date) as Week
FROM TestTable AS T
GROUP BY DATEPART(Year, W.Date), DATEPART(WEEK, W.Date)

月別

SELECT 
	count(*) as cnt,
	DATEPART(Year, W.Date) as Year, DATEPART(Month, W.Date) as Month
FROM TestTable AS T
GROUP BY DATEPART(Year, W.Date), DATEPART(Month, W.Date)

四半期別

SELECT 
	count(*) as cnt,
	DATEPART(Year, W.Date) as Year, DATEPART(quarter, W.Date) as Quarter
FROM TestTable AS T
GROUP BY DATEPART(Year, W.Date), DATEPART(quarter, W.Date)

年別

SELECT 
	count(*) as cnt,
	DATEPART(Year, W.Date) as Year
FROM TestTable AS T
GROUP BY DATEPART(Year, W.Date)