SQL Server Select query for multiple count() based on column value

gz5pxeao 于 2023-04-10 发布在其他

关注(0)|答案(3)|浏览(127)

I have a SQL Server table Orders with data as shown here. I need to get counts for particular column based value and calculate sum of those counted columns.

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

Now I want to get each status column with its count as individual column and need to calculate the Total number of Status counts in a separate column as mentioned below.

Result should return values like this:

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

I have tried something with count() but was not able to make the columns with row values for Status column in table Orders . I need some guidance on how to make row values as columns as mentioned with above conditions with grouping of Status and Year columns.

sql-server

来源：https://stackoverflow.com/questions/75957035/select-query-for-multiple-count-based-on-column-value

3条答案

按热度按时间

31moq8wy1#

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

This query will count the number of orders with each status for each year, and display the results in a table with columns for Success, Failed, NotRun, Total, and Year. The results will be grouped by Year.

赞(0）回复(0）举报 2023-04-10

sulc1iza2#

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

This will work when you have only 4 types of status.

赞(0）回复(0）举报 2023-04-10

ou6hu8tu3#

This is the same as the other answers but I added logic to not count nulls in the total count.

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

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server Select query for multiple count() based on column value

3条答案

相关问题

热门标签

最新问答