CODEWITHSUNDEEP
sqlsql-server
Emre Acar
Emre Acar

Reputation: 920

Getting two completely different columns in one query

I'm developping an asp.net project. I need to filter database with two different conditions and show each in a pie chart. So i need to get two columns in one query.

Column 1 :

select COUNT (*) 'OAS' from atmterminalfile (nolock) where Atstatu='2' and atlinestat ='1'

Column 2 : 

select COUNT (*) 'OFS'  from atmterminalfile (nolock) where Atstatu='2' and atlinestat ='2'

I searched many solutions, i tried UNION but result was this

|      OAS       |
|----------------|
| Column 1 Count |
| Column 2 Count |

i just need this. 

|      OAS         |        OFS       |
---------------------------------------
| Column 1 Count   |  Column 2 Count  |

Upvotes: 1

Views: 87

Answers (2)

podiluska
podiluska

Reputation: 51514

While the accepted answer will work, this looks like a prime case for a PIVOT query.

select * 
from atmterminalfile 
pivot (count(id) for atlinestat in ([1],[2])) p

Upvotes: 0

juergen d
juergen d

Reputation: 204924

select sum(case when atlinestat = 1 then 1 else 0 end) 'OAS',
       sum(case when atlinestat = 2 then 1 else 0 end) 'OFS' 
from atmterminalfile (nolock) 
where Atstatu='2' 
and atlinestat in (1,2)

Upvotes: 5

Related Questions