1.sql

gistfile1.txt

SELECT COUNT(enrolment.id), au.name FROM q_dc_enrolment enrolment
INNER JOIN q_ob_person person ON person.id = enrolment.person_id
INNER JOIN q_od_personaddress person_address ON person_address.id = 
(SELECT MAX(id) FROM q_od_personaddress pa WHERE pa.person_id = enrolment.person_id GROUP BY pa.person_id)
INNER JOIN (
  WITH RECURSIVE recursetree(id, parent_id, path, name) AS (
    SELECT 
        id, 
        parent_id, 
        array[id] AS path, 
        name 
    FROM q_ob_adminunit WHERE parent_id = 1 --ID OF THE ROOT admin unit
  UNION ALL
    SELECT t.id, t.parent_id, rt.path || t.id, t.name
    FROM q_ob_adminunit t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
  SELECT path[1] AS admin_unit_id, id, name FROM recursetree ORDER BY path
) AS admin_unit_root ON admin_unit_root.id = person_address.adminunit_id
INNER JOIN q_ob_adminunit au ON au.id = admin_unit_root.admin_unit_id
GROUP BY au.name