Struggling with Grouping 1

[Halliarse]

Good Evening

I have a table which contains a PreparedBy column which contains names, a column Accepted which is either Y, N or Null.

I need to select records, grouped by PreparedBy and and for each group show either the number Won or Lost. The Won is determined if the Accepted column holds a 'Y' and the Lost if it contains an 'N'. If it contains a NULL then neither sum is incremented.

I'm sure this should be quite easy but I can't get my head around it!

Any assistance would be great!

Thanks in advance

Steve

 

[gmmastros]

Select PreparedBy,
       Count(Case When Accepted = 'Y' Then 1 End) As CountWon,
       Count(Case When Accepted = 'N' Then 1 End) As CountLost
From   YourTableName
Group By PreparedBy

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Halliarse]

Hi George, thanks for the response, however the code generates the following error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Y' to data type bit.

 

[Halliarse]

Hi George

I have resolved it....there was rogue data in the table, one record containing a 'Y', however the field is actually a bit and contains 1 or zeroes!

Thanks for your help

Steve

 

[Olaf Doschke]

Bit fields can be compared to values 0,1 or NULL, but also 'True' and 'Falase' via implicit conversion.

You wrote about Y,N or NULL. That migt be, what's displayed, but not what's stored. Had Gammastros known the type of the field is bit, he wouldn't have written Accepted='Y' or 'N'

Bye, Olaf.