Wow there are LOTS of issue with your query. First of all you don't need to use a subquery for every single case expression. Just put the expression in your column. Then when grouping you can't group by a string value that is the name given to the column. Your grouping was trying to group by string literals instead of the results of your case expression.

With some formatting to make this more legible your final query should be something like this.

select

d.dealerId as 'Dealer ID'

, d.name as 'Dealer Name'

, d.address as 'Address'

, d.city as 'City'

, d.state as 'State'

, d.postalCode as 'Zip Code'

, d.country as 'Country'

, d.phone as 'Phone'

, case when d.isCertified = 0 then 'No' else 'Yes' end as 'Certified Dealer'

, case when d.isCertified = 0 and isDate(d.dateCertified) = 1 then 'Yes' else 'No' end as 'Was Certified Dealer'

, case when d.dr360statusid = 0 then 'No' else 'Yes' end as '360Certified'

, case when d.isServiceCenterCertified = 0 then 'No' else 'Yes' end as 'CSC Dealer'

, d.reviewCountOverall as 'Total # of Reviews'

, d.reviewCountTwentyFourMonths as '24 Month # of Reviews'

, d.ratingOverall as '24 Month Rating'

, d.ratingNewSales as '24 Month Sales New Rating'

, d.ratingUsedSales as '24 Month Sales Used Rating'

, d.ratingService as '24 Month Service Rating'

, count(e.iscertified) as 'Certified Employees'

, case d.IsCertified when 1 then IsNull(datediff(dd, d.dateCertified, getDate()), 0) end as 'Days Certified'

from dealer d

Left join dealercontacts e ON e.dealerid = d.dealerid

where d.statusId = 1

and d.dealerid in

(

11227

, 11945

, 9614

)

group by d.dealerId

, d.name

, d.address

, d.city

, d.state

, d.postalCode

, d.country

, d.phone

, case when d.isCertified = 0 then 'No' else 'Yes' end

, case when d.isCertified = 0 and isDate(d.dateCertified) = 1 then 'Yes' else 'No' end

, case when d.dr360statusid = 0 then 'No' else 'Yes' end

, case when d.isServiceCenterCertified = 0 then 'No' else 'Yes' end

, d.reviewCountOverall

, d.reviewCountTwentyFourMonths

, d.ratingOverall

, d.ratingNewSales

, d.ratingUsedSales

, d.ratingService

, case d.IsCertified when 1 then IsNull(datediff(dd, d.dateCertified, getDate()), 0) end

order by d.name asc