SQL分组查询

--按半年分组查询数据
SELECT SUM(CONVERT(DECIMAL(18,2),TradeArea)) SumArea,SUM(convert(decimal(18,2),TradeTotalPrice)) SumPrice,COUNT(KeyId) AS HouserNumber,
LEFT(CONVERT(VARCHAR(100), SignDate, 23),4)+'年' years,
CASE when substring(CONVERT(varchar(100), SignDate, 23),6,2) <= '06' then '上半年' else '下半年' end as months
FROM Est_AmountPriceTrend
WHERE SignDate>='2017-06-01'
GROUP BY left(CONVERT(varchar(100), SignDate, 23),4),case when substring(CONVERT(varchar(100), SignDate, 23),6,2) <= '06'
then '上半年' else '下半年' end ORDER BY years
--按年分组查询数据
SELECT YEAR(SignDate) Name,COUNT(KeyId)HouserNumber ,SUM(convert(decimal(18,2),TradeArea)) SumArea,SUM(convert(decimal(18,2),TradeTotalPrice)) SumPrice FROM Est_AmountPriceTrend
WHERE SignDate>='2017-06-01' GROUP BY YEAR(SignDate) ORDER BY Name
--按月分组查询数据
select tb.signdate AS Name,SUM(convert(decimal(18,2),tb.TradeArea)) SumArea,SUM(convert(decimal(18,2),tb.TradeTotalPrice)) SumPrice,
COUNT(tb.KeyId) HouserNumber
from (SELECT convert(varchar(7),signdate,120) signdate,TradeArea,TradeTotalPrice,TradeUnitPrice,KeyId
FROM Est_AmountPriceTrend m WHERE SignDate>='2017-06-01' )tb
GROUP BY tb.signdate order by tb.signdate DESC

--按季度分组查询数据
SELECT
DATENAME(year,t.SignDate) years,
DATENAME(quarter,t.SignDate) months,
SUM(convert(decimal(18,2),TradeArea)) SumArea,
COUNT(KeyId) AS HouserNumber,
SUM(convert(decimal(18,2),TradeTotalPrice)) SumPrice
FROM Est_AmountPriceTrend t(NOLOCK) WHERE SignDate>='2017-06-01' GROUP BY DATENAME(year,SignDate),DATENAME(quarter,SignDate) ORDER BY years,months
--按周分组查询数据
SELECT SUM( SumArea)SumArea,SUM(SumPrice) SumPrice,COUNT(t.KeyId) AS HouserNumber,年 AS years,周 AS months ,weekone,t.weekend
FROM (
SELECT convert(decimal(18,2),TradeArea) SumArea,
DATEPART (week,SignDate) '周',year(SignDate) '年'
,CONVERT(varchar(10),SignDate-(datepart(weekday,SignDate)-2),120)as weekone,
convert(varchar(10),SignDate+(8-datepart(weekday,SignDate)),120)as weekend
,convert(decimal(18,2),TradeTotalPrice) SumPrice,KeyId
from Est_AmountPriceTrend WHERE SignDate>='2017-06-01') t GROUP BY 年,周 ,weekone,t.weekend

--按自定义名称分组查询
select Name,OrderByName ,SUM(convert(decimal(18,2),TradeArea)) SumArea,COUNT(KeyId) AS HouserNumber,SUM(convert(decimal(18,2),TradeTotalPrice)) SumPrice
from (SELECT *,case
WHEN TradeUnitPrice >=0 And TradeUnitPrice <=10000 THEN '1万以下'
WHEN TradeUnitPrice >=10000 And TradeUnitPrice <=15000 THEN '1-1.5万'
WHEN TradeUnitPrice >=15000 And TradeUnitPrice <=20000 THEN '1.5-2万' end as name,
case
WHEN TradeUnitPrice >=0 And TradeUnitPrice <=10000 THEN 0
WHEN TradeUnitPrice >=10000 And TradeUnitPrice <=15000 THEN 1
WHEN TradeUnitPrice >=15000 And TradeUnitPrice <=20000 THEN 1.5 end as OrderByName
from Est_AmountPriceTrend ) temp where 1=1 and signdate>='2017-06-01'
And TradeUnitPrice >=0 AND TradeUnitPrice<= 10000 OR TradeUnitPrice >=10000 AND
TradeUnitPrice<= 15000 OR TradeUnitPrice >=15000 AND TradeUnitPrice<= 20000
GROUP by name,OrderByName order by OrderByName