Probably not very performant, but you could always use:

;with customer_name as

(

SELECT *,SUM(rendered) OVER(PARTITION BY customername) as totalrendered from #customer

)

SELECT

customer.customername

,customer.serviceid

,serv.servicename

FROM customer_name customer

INNER JOIN #services serv on serv.serviceid = customer.serviceid

WHERE totalrendered =...

WIthout seeing some sample data, I would guess that something like this would work then:

Select CREATE_OFFICE_TEAM_CD, COUNT(*) as number_of_office_team from MF_FORUM

where WORKFLOW_ACTION_CD IN ('D', 'A')

and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT',...

The issue seems to be that you are grouping by the field which shows the aggregated value

Try Select count(CREATE_OFFICE_TEAM_CD), CREATE_DT as create_dt from MF_FORUM

where WORKFLOW_ACTION_CD IN ('D', 'A')

and CREATE_OFFICE_TEAM_CD...

Thanks both - turns out it was a stupid typo making it not work in the end - d'oh!