Question: I have found select statements that basically use different where clauses to count. My question is, how do I combine the results in one statement so that these counts can become columns?

1. select count(\*) as c1 from table1 where city = 'nyc'
2. select count(\*) as c2 from table1 where city = 'boston'
3. select count(\*) as c3 from table1 where city = 'sf'

Answer: 
```
SELECT
  COUNT(CASE WHEN city = 'nyc' THEN 1 END) AS Nyc,
  COUNT(CASE WHEN city = 'boston' THEN 1 END) AS Boston,
  COUNT(CASE WHEN city = 'sf' THEN 1 END) AS Sf
FROM table

```
```
select count(CASE WHEN city = 'nyc' THEN 1 END) as c1,
       count(CASE WHEN city = 'boston' THEN 1 END) as c2,       
       count(CASE WHEN city = 'sf' THEN 1 END) as c3
from table1

```

Demo on [**SQLFiddle**](http://sqlfiddle.com/#!3/b5a87/1)

Also in SQLServer2005+, Oracle you can use [PIVOT](http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx) operation

```
SELECT *
FROM table1
PIVOT (
COUNT(city) FOR city IN ([nyc], [boston], [sf])
) p

```

Demo on [**SQLFiddle**](http://sqlfiddle.com/#!3/b5a87/2)