How to count multiple values ​​from a column using the SQL Server CASE keyword?

advertisements

I want to display a result like this:

My data for each column is as follows:

CustomerID  FirstName   Total_Count_Status ResultMessage
  1          Noor        10                Completed Calls
  2          ALi          6                InProgress Calls
  3          Jhon         9                CallBack  Calls
  4          Maaan        1                MoneyCall Calls
  5          samuel       5                Cancelled Calls
  6          Khan         3                Dispatched Calls

In my workorder table I have the OrderStatusID column data:

OrderStatusID
3
2
3
5
7

I have the following Hardcoded values for my Case:

for Pending = 0,
for Dispatched = 1,
    InProgress = 2,
    Complete = 3,
    Estimate = 4,
    CallBack = 5,
    MoneyCall = 6,
    Cancelled = 7,

This will be used for show up their status.

i have my table name : WorkOrder which has column OrderStatusID i want to count OrderstatusID for each

if OrderStatusID='3' we should have to count all 3 values of orderstatusID column and display result like in the above table . same is for other ..values

How i can create Proc for situation like this ??Thanks

Any help will be highly appreciated.


Part of the syntax is:

 CASE WHEN <condition> THEN <expression> ELSE <expression> END

The trick is to convert counts into sums and to evaluate the expressions as 0 or 1:

SELECT ...,
       COUNT(CASE WHEN OrderStatusID = 3 THEN 1 ELSE 0) AS CompletedCalls,
       COUNT(CASE WHEN OrderStatusID = 2 THEN 1 ELSE 0) AS InProgressCalls,
       ...

You'll need your FROM, WHERE, and GROUP BY clauses as usual.