sql - Subquery aggregate function with SUM(CASE SUBQUERY) -

i'm getting error while executing query

cannot perform aggregate function on expression containing aggregate or subquery.

code:

select      s.id,     sum(case when sc.coverage in (select number arrayofintegersfromstring(@dynamicdata)) 1 else 0 end) sm      storefronts s     left join storefrontcoverages sc on s.id = sc.storefront     left join vendors v on s.vendor = v.id (     v.active = 1     ,     s.approved = 1     ,     s.status = 1  ) group s.id having sum(case when sc.coverage in (select number arrayofintegersfromstring(@dynamicdata)) 1 else 0 end)  > 0 order sm desc 

sum in select not important 1 in having, if can me out without sum(...) in select helpful.

i think better move subquery from clause. based on logic, looking joins, not left joins -- after all, having clause looking match.

i think following query want:

select s.id, count(dd.number) sm storefronts s join      storefrontcoverages sc      on s.id = sc.storefront join      (select number arrayofintegersfromstring(@dynamicdata)      ) dd(number)      on sc.coverage = dd.number s.approved = 1 , s.status = 1 ,       exists (select 1               vendors v               s.vendor = v.id ,                     v.active = 1              ) group s.id order sm desc;