Group By and Count the total number of same status value

user_4665467 0
Viewed 88

I have 2 tables, Order and Store. And here are some of the fields from the table I need.

Order table

  OrderId StoreId SystemOrderStatus
     1       1        Received
     2       1        Sent
     3       2        Complete
     4       2        Received

How can I achieve to have this output:

StoreId ReceivedStatusCount SentStatusCount CompleteStatusCount
     1            1                1                0
     2            1                0                1

Thank you.

count linq-to-sql group-by sql c#
1 Answers

Just use case when, as blow:

select
    StoreId,
    sum(case when SystemOrderStatus='Received' then 1 else 0 end) as ReceivedStatusCount,
    sum(case when SystemOrderStatus='Sent' then 1 else 0 end) as SentStatusCount,
    sum(case when SystemOrderStatus='Complete' then 1 else 0 end) as CompleteStatusCount
from
    "Order"
group by
    StoreId
order by
    StoreId
user_88973510
Related