Flatten results 1

[nickdel]

I have the following query which counts the instances of a certain field. This works just fine however it returns the results on 3 lines and what I need it to do it return this on just one line:

select	ofr.officername,
	sum(case PCRatingID
		when 1 then 1 else 0
	end) as 'Excellent',
	sum(case PCRatingID
		when 2 then 1 else 0
	end) as 'Satisfactory',
	sum(case PCRatingID
		when 3 then 1 else 0
	end) as 'Unsatifactory'
		
from	CustomerBaseMain cbm left join officers ofr
		on cbm.officerid = ofr.officerid 
	inner join Plausibility pl
		on cbm.AccountID = pl.AccountID
where	cbm.actiondate between '01SEP2006' and '12APR2007'
and 	cbm.officerid in ('123456','654321','123654')
Group By ofr.officername, PCRatingID

All input appreciated!

Thanks

 

[r937]

here you go, output on just one line --

select sum(case PCRatingID
           when 1 then 1 else 0 end) as 'Excellent'
     , sum(case PCRatingID
           when 2 then 1 else 0 end) as 'Satisfactory'
     , sum(case PCRatingID
           when 3 then 1 else 0 end) as 'Unsatifactory'
  from CustomerBaseMain cbm 
inner 
  join Plausibility pl
    on pl.AccountID = cbm.AccountID
left 
  join officers ofr
    on ofr.officerid = cbm.officerid 
 where cbm.actiondate 
       between '01SEP2006' and '12APR2007'
   and cbm.officerid 
       in ('123456','654321','123654')

r937.com | rudy.ca