GROUP BY与aggregate SQL Server
GROUP BY与aggregate SQL Server
提问于 2016-08-09 04:15:02
我有一个复杂的查询，我已经将其转储到一个临时表中。
在这个数据集中，我有不同类型的交易:电影，货到付款，功能，其他。
所有totalrev_YTD在10K和15K之间的交易应该汇总到一行中，交易名称为"TOTAL COD“，否则它应该有自己的行。
我该怎么做呢。我似乎不能使用10k到15k之间的SUM(totalrev_YTD)来做GROUP BY。
有没有人可以帮忙：
SELECT
location, locationid, dealtype,
(CASE
WHEN dealtype = 'OTHER (COD, ETC)' and totalrev_YTD BETWEEN 10000 AND 15000
THEN 'OTHER (COD, ETC)'
ELSE deal
END) as deal,
rental_PW, rental_MTD, rental_QTD, rental_YTD,
sales_PW, sales_MTD, sales_QTD, sales_YTD,
otherrev_PW, otherrev_MTD, otherrev_QTD, otherrev_YTD,
totalrev_PW, totalrev_MTD, totalrev_QTD, totalrev_YTD
FROM
#temp_rev t1
WHERE
dealtype = 'OTHER (COD, ETC)'
GROUP BY
(CASE
WHEN dealtype = 'OTHER (COD, ETC)' and totalrev_YTD BETWEEN 10000 AND 15000
THEN 'OTHER (COD, ETC)'
ELSE deal
END),
location, locationid, dealtype,
rental_PW, rental_MTD, rental_QTD, rental_YTD,
sales_PW, sales_MTD, sales_QTD, sales_YTD,
otherrev_PW, otherrev_MTD, otherrev_QTD, otherrev_YTD,
totalrev_PW, totalrev_MTD, totalrev_QTD, totalrev_YTD举个例子，我有10个货到付款，其他的交易。其中1笔交易的totalrev_YTD > 15K。在这种情况下，它应该与它自己的行一起出现，而另一个应该只是聚合。
回答 1
Stack Overflow用户
发布于 2016-08-09 04:31:08
我认为您的GROUP BY很好，您只需要在较少的列上使用它，然后您聚合(使用SUM)所有应该是相加的数字。
在“单行”交易的情况下，查询将只对一行求和，这是完全可以的。
示例：
SELECT location, locationid, dealtype
, (CASE WHEN dealtype = 'OTHER (COD, ETC)' and totalrev_YTD BETWEEN 10000 AND 15000 THEN 'OTHER (COD, ETC)'
ELSE deal
END
) as deal,
sum(rental_PW) as rental_PW,
sum(rental_MTD) as rental_MTD,
..., -- insert other summed columns here
sum(totalrev_YTD) as totalrev_YTD
FROM #temp_rev t1
WHERE dealtype ='OTHER (COD, ETC)'
GROUP BY
(CASE WHEN dealtype = 'OTHER (COD, ETC)' and totalrev_YTD BETWEEN 10000 AND 15000 THEN 'OTHER (COD, ETC)'
ELSE deal
END
) ,
location, locationid, dealtype页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38837761
复制相关文章
相似问题
腾讯云开发者
