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