我有一个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
列的行值。我需要一些指导,如何使行值作为列与上述条件所提到的Status
和Year
列分组。
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