CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Hernan Bravo
Hernan Bravo

Reputation: 31

How do I combine 3 select statements into one? (SQL Server 2008)

I have 4 sql statements, each returns 2 columns

select count(id) open, sum(amount) from A where status = 1 group by est_id

result: 25 | 4400

select count(id) close, sum(amount) from A where status = 2 group by est_id

result: 0 | 0

select count(id) stop, sum(amount) from A where status = 3 group by est_id

result: 20 | 4000

I need to return the result of the 4 sql sql statement in one, for example:

25 | 4400| active
0  | 0   | inactive
20 | 4000| close

regards

Upvotes: 0

Views: 113

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5498

As mentioned by @bluefeet you're looking for the UNION ALL operator.

select count(id) as c1, sum(amount) as c2, 'active' as c3 from A where status = 1 group by est_id
union all
select count(id), sum(amount), 'inactive' from A where status = 2 group by est_id
union all
select count(id), sum(amount), 'close' from A where status = 3 group by est_id

The column names in the combined resultset will be taken from the first component resultset.

Upvotes: 1

Related Questions