SQLTeam.com | Weblogs | Forums

Combining two queries


#1

Does anyone know how to combine two very simple queries? So something like this...

Query 1
SELECT City, Amount
FROM Table1
WHERE Status.Table1 = 'OK'

Results
City | OK Amount
London | 300
Paris | 200

Query 2
SELECT City, Amount
FROM Table1
WHERE Status.Table1 = 'Error'

Results
City | ErrorAmount
London | 150
Tokyo | 400

So I want to combine these so that it looks like this:

City | OK Amount | Error Amount
London | 300 | 150
Paris | 200 | 0
Tokyo | 0 | 400

The thing is the Amount column is just one column in the table. but in the results I would like it split depending on another column criteria (OK or Error)

Thanks in advance


#2
select 
	City,
	SUM(case when Status = 'OK' then Amount else 0 end) as OKAmount,
	sum(case when status = 'Error' then Amount else 0 end) as ErrorAmount
from
	Table1
group by
	City;

#3

Awesome, thanks!