按订单金额统计销售数量
按订单金额统计销售数量
提问于 2019-05-23 01:10:03
我正在使用SQL Server2008 R2，并对包含CustomerID、OrderAmount、RegionID的表进行分析。我需要根据每个地区的OrderAmount来统计不同类别的订单数量。如果该类别中没有sales，则返回0。
Sample of data:
CustomerID | OrderAmount | RegionID
10001 | 50 | 801
10002 | 25 | 801
10003 | 200 | 802
10001 | 100 | 802
10002 | 20 | 802
...And my expected result is:
RegionID | CategoryID | Num_of_Sales
801 | 1 | 2 -----Below 100
801 | 2 | 0 -----100-200
802 | 1 | 2 -----Below 100
802 | 2 | 1 -----100-200
...我的问题是: 1.如何在为空的类别上返回0? 2.有没有更好的方法来编写代码？(不使用UNION)
WITH Category1 AS(
SELECT * FROM Sales_Table
WHERE NewAmount <= 100
)
, Category2 AS(
SELECT * FROM Sales_Table
WHERE NewAmount BETWEEN 101 AND 200
)
, [...]
SELECT Region_ID, CategoryID, Num_of_Sales
FROM (
SELECT Region_ID, COUNT(*) AS [Num_of_Sales], 1 AS CategoryID
FROM Category1
GROUP BY Region_ID
UNION
SELECT Region_ID, COUNT(*) AS [Num_of_Sales], 2 AS CategoryID
FROM Category2
GROUP BY Region_ID
UNION
[...]
)z
ORDER BY Region_ID, CategoryID
所以，我使用这些代码并得到了我的结果，但是在区域801的100-200类别上，计数没有返回0。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-05-23 03:14:12
需要一个包含RegionID和CategoryID的表来实现您想要实现的目标。然后，我们可以使用该表进行连接，如下所示。
With RegCatSales as
(
select RegionID,C,COUNT(*) AS [Num_of_Sales]
from
(
select RegionID,OrderAmount,
CASE
WHEN OrderAmount <= 100 THEN 1
WHEN OrderAmount BETWEEN 101 AND 200 THEN 2
END as C
from Sales_Table x
) xx
group by RegionID, C
),
Regions as
(
select distinct RegionID from RegCatSales
),
Categories as
(
select distinct C from RegCatSales
),
RegCat AS(
select distinct RegionID, C as CategoryID from Regions,Categories
)
select rc.RegionID,rc.CategoryID, ISNULL([Num_of_Sales],0) NUM_Of_Sales from
RegCatSales rcs
right join RegCat rc
on rc.RegionID= rcs.RegionID and rc.CategoryID = rcs.C
order by rc.RegionID, rc.CategoryID页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56261870
复制相关文章
相似问题
腾讯云开发者
