sql - Returning count using multiple subqueries -

forgive ignorance, sql-server not strong suit. i'm trying retrieve count of rows 2 mapping tables using subqueries (correlated subqueries?). if remove 1 subquery, things work fine, if include both subqueries, i'm not getting expected number of rows back.

select      a.id,     a.name,     al_1.locationid locationcount,     af_1.funcareaid funcareacount      application inner join     applicationlocation al_1 on a.id = al_1.appid inner join     applicationfuncarea af_1 on a.id = af_1.appid     al_1.locationid in         (             select count(locationid)             applicationlocation al_2             al_2.appid = al_1.appid         ) ,      af_1.funcareaid in          (             select count(funcareaid)             applicationfuncarea af_2             af_2.appid = af_1.appid         ) 

if i'm not mistaken want achieve here, can achieved using grouping count , distinct operators:

select      a.id,     a.name,     count(distinct al_1.locationid) locationcount,     count(distinct af_1.funcareaid) funcareacount     application inner join     applicationlocation al_1 on a.id = al_1.appid inner join     applicationfuncarea af_1 on a.id = af_1.appid group a.id, a.name 

it hard tell if result correct without seeing data , expected result. if above not work, try removing distinct operators , see if results need.