CODEWITHSUNDEEP
sql-servercount
Umer Ikhlas
Umer Ikhlas

Reputation: 13

Need query for counting for 2 columns at a time

I have a table named "Orders"

It has following fields: OrderID, OrderDate, ..... ,City, StatusID.

I want this result as return:

City        No. of Delivered Orders, No. of Pending (Not Delivered)
-------------------------------------------------------------------
London      3                        4
Paris       5                        6
New York    7                        8

Since we have only one field to track the delivery status that is StatusID, so I am facing difficulty in order to count for two conditions at a time..

Thanx in Advance :)

Upvotes: 1

Views: 34

Answers (1)

dean
dean

Reputation: 10098

select City,
sum(case when StatusID = 'delivered' then 1 else 0 end) as [No. of Delivered Orders],
sum(case when StatusID = 'not_delivered' then 1 else 0 end) as [No. of Pending]
from Orders

Upvotes: 1

Related Questions