Need to SUM of Multiple Rows

Transact-SQL
1

Hi Guys,

There is a table which consist of Assigned To, Open, Close, Void, In Progress.
I need to Create a Table Which needs to Group Based on Assigned To and SUM of each Rows.

I Already wrote to the code but unable to SUM of All Rows. Kindly help to SUM of All rows, So that the Total should be in new Column as "Total".

< />
Select Status, count(Assigen_To) from Detail
group by Status

;with cte as (
Select Assigen_To, count(Assigen_To) [Open], 0 [Close], 0 [VOID], 0 [IN PROGRESS] from Detail
where Status = 'Open'
group by Assigen_To
union All
Select Assigen_To, 0 [Open], count(Assigen_To) [Close], 0 [VOID], 0 [IN PROGRESS] from Detail
where Status = 'CLOSE'
group by Assigen_To
union All
Select Assigen_To, 0 [Open], 0 [Close], count(Assigen_To) [VOID], 0 [IN PROGRESS] from Detail
where Status = 'VOID'
group by Assigen_To
union All
Select Assigen_To, 0 [Open], 0 [Close], 0 [VOID], count(Assigen_To) [IN PROGRESS] from Detail
where Status = 'IN PROGRESS'
group by Assigen_To
)
--Select * from cte

Select Assigen_To, SUM([Open]) [Open], SUM([Close]) [Close], SUM(VOID) [VOID], SUM([IN PROGRESS]) [IN PROGRESS] from cte
group by Assigen_To

</>

2

I think this is what you are looking for:

SELECT Assigen_To
     ,  SUM(CASE WHEN Status = 'Open' THEN 1 ELSE 0 END) AS Open
     ,  SUM(CASE WHEN Status = 'Close' THEN 1 ELSE 0 END) AS Close
     ,  SUM(CASE WHEN Status = 'Void' THEN 1 ELSE 0 END) AS Void
     ,  SUM(CASE WHEN Status = 'In Progress' THEN 1 ELSE 0 END) AS InProgress
     ,  COUNT(*) AS Total
  FROM Detail
 GROUP BY Assigen_To;
3

This code throws syntax error near keyword 'Open'

4

SUM(CASE WHEN Status = 'Open' THEN 1 ELSE 0 END) AS OpenStatus

Or - use quotes:

SUM(CASE WHEN Status = 'Open' THEN 1 ELSE 0 END) AS 'Open'

Or use brackets:

SUM(CASE WHEN Status = 'Open' THEN 1 ELSE 0 END) AS [Open]

1 Like
5

Thanks a lot @jeffw8713. It works now :smile: