选择基于列值的多个count()查询



我有一个SQL Server表Orders,其中的数据如下所示。我需要获得基于特定列值的计数,并计算这些计数列的总和。

ID | Name   | Status  |  Year
---+--------+---------+------
1  |ckOrder | Success |  2023
2  |psOrder | Failed  |  2023
3  |saOrder | Failed  |  2022
4  |skOrder | Failed  |  2023
5  |pkOrder | NotRun  |  2023
6  |plOrder | Success |  2023
7  |poOrder | Null    |  2023

现在我想获得每个状态列,并将其计数作为单独的列,并且需要计算单独列中的状态计数总数,如下所述。

结果应该返回如下值:

Success | Failed | NotRun | Total | Year
--------+--------+--------+-------+-----
2       |   3    |    1   |   6   | 2023
0       |   0    |    1   |   1   | 2022

我已经尝试了count()的东西,但无法使列与Orders表中Status列的行值。我需要一些指导,如何使行值作为列与上述条件所提到的StatusYear列分组。

SELECT 
COUNT(CASE WHEN Status = 'Success' THEN 1 END) as Success,
COUNT(CASE WHEN Status = 'Failed' THEN 1 END) as Failed,
COUNT(CASE WHEN Status = 'NotRun' THEN 1 END) as NotRun,
COUNT(*) as Total,
Year
FROM Orders
GROUP BY Year
ORDER BY Year DESC

该查询将计算每年每种状态下的订单数量,并在包含Success、Failed、NotRun、Total和year列的表中显示结果。结果将按年份分组。

try this query:

SELECT 
SUM(CASE WHEN Status = 'Success' THEN 1 ELSE 0 END) as Success, 
SUM(CASE WHEN Status = 'Failed' THEN 1 ELSE 0 END) as Failed, 
SUM(CASE WHEN Status = 'NotRun' THEN 1 ELSE 0 END) as NotRun, 
COUNT(*) as Total,
Year
FROM Orders
GROUP BY Year, Status

当你只有4种类型的状态时,这将有效。

这与其他答案相同,但我添加了在总数中不计算null的逻辑。

SELECT COUNT(CASE WHEN Status = 'Success' THEN 1        END) AS Success
, COUNT(CASE WHEN Status = 'Failed'  THEN 1        END) AS Failed
, COUNT(CASE WHEN Status = 'NotRun'  THEN 1        END) AS NotRun
, COUNT(CASE WHEN Status IS NULL     THEN 0 ELSE 1 END) AS Total
, Year
FROM Orders
GROUP BY Year
ORDER BY Year Desc

相关内容

  • 没有找到相关文章

最新更新