Thursday 19 May 2011

Multiple Count from Single Table Sql Server

Recently my collegue was working with a query where he needed multiple counts from a table with different conditions. Say its something like a company has multiple office buildings with multiple floors. They need the count of total space for each floor of each building and empty space with the same conditions.
The query I referred was like this:

SELECT SUM(CASE WHEN condition1 THEN 1 ELSE 0 END) count1, SUM(CASE WHEN condition2 THEN 1 ELSE 0 END) count2 FROM yourtable

Customizing it to my need I wrote the following query

SELECT SUM(CASE SeatStatus WHEN 0 THEN 1 ELSE 0 END) FreeSpace, COUNT(Seat) TotalSpace FROM MyTable

No comments: