MSSQL builtin clause ... count query

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 2000 Forums

Transact-SQL (2000)

MSSQL builtin clause ... count query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-13 : 08:01:29

Robert writes "ok this is probably simple but driving me nuts on how to build a query in ms sql server to handle mutiple count types for the same column data.

If I have a table that say has two columns such as
'A', 'Yes'
'A', 'Yes'
'A', 'No'
'B', 'No'
'B', 'No'
'B', 'No'

Id like a mssql query that gives percentages based on values grouped by the first column ... to follow our example
Col1 .... NoPercentage ..... YesPercentage
----- .... --- ............... ---
A .... 33.3 ............... 66.3
B ..... 100.0 .............. 0.0

The closest I can get is

Col1 ..... NoCountPErcentage
----- ..... -----------------
A ..... 33.3
B ..... 100.0

or

Col1 .... YesCountPercentage
----- .... ------------------
A .... 66.3
B .... 0.0

But I cannot get them on same MS sql query ... my query looks like the following and report varies by the where clause but cannot vary within the count clause ...

select Col1, CAST(((CAST(count(*) as decimal(10,2))/3)*100) as decimal(10,1)) as NoCountPercentage from PCI_Score where PCIRequirementResponse = 'No'
group by appName

Your help is most appreciated.

Robert"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-13 : 08:09:04

Select Col1,Sum(Case when col2='Yes' then 1 else 0 end) as YesCount,
Sum(Case when col2='No' then 1 else 0 end) as NoCount from yourTable

Based on this you can calculate rest

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources