what do you want the result to look like?

You could do...

Select Column1,
Column2,
Column3,
Coalesce(column4,'NULL') as [Column4alias],
Count(Coalesce(column4,'NULL')) as [Countof4]
From @t TestTable
Group By Column1, Column2, Column3,Coalesce(column4,'NULL')

This will give you a result that looks like.. and you
don't need to worry about generating a case for each
of the 15 values you might have..

Column1 Column2 Column3 Column4alias Countof4
------- ------- ------- ------------ --------
val1 val2 val3 Y 782
val1 val2 val3 N 532
val1 val2 val3 NULL 526
val1 val2 val3 M 352
val1 val2 val4 Y 17
val1 val2 val4 N 25
val1 val2 val5 Y 147
val1 val2 val6 M 5



-----Original Message-----
From: Jumma Salim
[mailto:mssqldba-ezmlmshield-x9274574.[Email address protected]
Sent: Thursday, July 28, 2005 10:53 AM
To: LazyDBA Discussion
Subject: RE: some problem



What happens when there are more than 3 values say 15 values
sj

-----Original Message-----
From: Coley Todd
[mailto:mssqldba-ezmlmshield-x21043615.[Email address protected]
Sent: Thursday, July 28, 2005 10:16 AM
To: LazyDBA Discussion
Subject: RE: some problem

My personal preference is

Select Column1, Column2, Column3,
Sum(Case when Column4 = 'Y' Then 1 Else 0 End) AS 'Yes',
Sum(Case when Column4 = 'N' Then 1 Else 0 End) AS 'No',
Sum(Case when Column4 Is Null Then 1 Else 0 End) AS 'Null'
From @t TestTable
Group By Column1, Column2, Column3

Todd

-----Original Message-----
From: Jumma Salim
[mailto:mssqldba-ezmlmshield-x4067602.[Email address protected]
Sent: Thursday, July 28, 2005 8:03 AM
To: LazyDBA Discussion
Subject: RE: some problem

Sorry for being criptic but here it my question

Will this query work if not what will?
SELECT column1,column2 ,column3,
count ('column4alias' =
case
when column4 IS 'Y' then 'yes'
when column4 IS 'N' then 'No'
else 'Null')

FROM [testtable]
group by column1,column2 ,column3,
FROM [Query5]
sj
-----Original Message-----
From: Michael Hessler
[mailto:mssqldba-ezmlmshield-x38052917.[Email address protected]
Sent: Wednesday, July 27, 2005 5:23 PM
To: LazyDBA Discussion
Subject: RE: some problem

SELECT
Yes = sum(case when (YN = ' Yes ') then 1 else '' end) , No = sum(case
when (YN = ' No ') then 1 else '' end) , NULLS = sum(case when YN is
NULL then 1 else '' end)
FROM Test_table1

-----Original Message-----
From: Jumma Salim
[mailto:mssqldba-ezmlmshield-x16252917.[Email address protected]
Sent: Wednesday, July 27, 2005 5:14 PM
To: LazyDBA Discussion
Subject: RE: some problem


How do I use count and case statement to figure out 'Yes'; 'No'; and
null
-----Original Message-----
From: Jumma Salim
[mailto:mssqldba-ezmlmshield-x39263952.[Email address protected]
Sent: Wednesday, July 27, 2005 3:12 PM
To: LazyDBA Discussion
Subject: some problem

Hello
I am doing a count for a column which has values 'Y';'N', 'Null'
And other columns for which I use group by column

Is it possible if so how do I displays the info as separate column

sj



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page