SQL统计

--按周统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
DATENAME(week,AddDate) 周,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate),DATENAME(week,AddDate)
ORDER BY CONVERT(INT,DATENAME(week,AddDate));

----------------------------------------------------------------
--按月统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
DATENAME(month,AddDate) 月,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate),DATENAME(month,AddDate)
ORDER BY CONVERT(INT,DATENAME(month,AddDate));

----------------------------------------------------------------
--按季度统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
DATENAME(quarter,AddDate) 季度,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate),DATENAME(quarter,AddDate)
ORDER BY CONVERT(INT,DATENAME(quarter,AddDate));

----------------------------------------------------------------
--按年统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2012-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate)
ORDER BY CONVERT(INT,DATENAME(year,AddDate));

转载于:https://www.cnblogs.com/fanling521/p/6075239.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值