SQL 按 年 季 月 统计

按年
SELECT year(CreationTime) 年次, count(1) 数据条数, sum(Num) 字段合计
FROM Orders 
GROUP BY year(CreationTime)

--按季统计 
SELECT datepart(quarter, CreationTime) 季次, count(1) 数据条数, sum(Num) 合计
FROM Orders 
WHERE year(CreationTime)=year(getdate()) 
GROUP BY datepart(quarter, CreationTime) 

--按月统计 
SELECT convert(char(7), CreationTime, 120) 月份,count(1) 数据条数, 数据合计=sum(Num) 
FROM Orders
GROUP BY convert(char(7), CreationTime, 120) 

--按日统计 
SELECT Convert(varchar(10),CreationTime,23) 日期, count(1) 销售次数, sum(Num) 销售量
FROM Orders
GROUP BY Convert(varchar(10), CreationTime, 23) 

--按日统计 out
日期            销售次数    销售量
2021-04-21    1    98
2021-04-24    1    82
2021-04-28    1    91
2021-05-01    1    98
2021-05-02    1    84
2021-05-05    1    76
2021-05-08    2    115
2021-05-10    1    32
2021-05-11    1    48
2021-05-12    1    39


--按周统计 (年度周次)
SELECT datepart(week, CreationTime) 周次, count(1) 销售次数, sum(Num) 销售量
FROM Orders 
WHERE year(CreationTime)=year(getdate()) 
GROUP BY datepart(week, CreationTime) 

--按周统计 (年度周次) out
周次    销售次数    销售量
17    2    180
18    2    189
19    4    275
20    3    119

--按周统计 (月份周次)
SELECT weekName 周次,count(1) 销售次数, sum(Num) 销售量 from (
SELECT cast(datepart(month,CreationTime) as varchar(2)) + '月第'+ cast((datepart(week,CreationTime) - datepart(week,convert(varchar(7),CreationTime,120) + '-01') + 1) as varchar(2)) + '周'  weekName,Num
FROM Orders 
WHERE year(CreationTime)=year(getdate()) 
)tb
GROUP BY weekName 


--可以创建函数来获取周次
CREATE FUNCTION fn_getweek
(
    @date datetime
)
RETURNS nvarchar(50)
AS
BEGIN
    DECLARE @result nvarchar(50)
    select @result=cast(datepart(mm,@date) as varchar(2)) + '月第' + cast((datepart(wk,@date) - datepart(wk,convert(varchar(7),@date,120) + '-01') + 1) as varchar(2)) + '周';
    -- Return the result of the function
    RETURN @result
END
GO
SELECT weekName 周次,count(1) 销售次数, sum(Num) 销售量 from (
SELECT dbo.fn_getweek(CreationTime) weekName,Num
FROM Orders 
WHERE year(CreationTime)=year(getdate()) 
)tb
GROUP BY weekName