select query doubt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select query doubt

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-02 : 00:32:19

Hello all,

I am having a table say TableX with ColA, ColB, ColC, ColD.
All four columns has possible values 'Yes','No','?' and null.

What I want is an output for with total number of records for which each column has value 'Yes'..

Something like::

ColA = 89
ColB = 34
ColC = 23
ColD = 11

I want them all in as 1 query output. Is there anyway that I can get two columns output as shown above.. doesn't have to include '=' sign.. just normal two columns output like that..

Thanks,
Ujjaval

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-02 : 00:36:38

[code]
select col = 'ColA', c = count(*)
from TableX
where ColA = 'Yes'

union all

select col = 'ColB', c = count(*)
from TableX
where ColB = 'Yes'

union all

select col = 'ColC', c = count(*)
from TableX
where ColC = 'Yes'

union all

select col = 'ColD', c = count(*)
from TableX
where ColD = 'Yes'
[/code]

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-02 : 01:12:07

Thanks for that. That worked. I thought union all works with combining columns of different tables in the output.. I guess need to look more on union all..

Cheers,

Ujjaval

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-02 : 01:18:09

union basically is to combine multiple results set to one, not restricted by tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources