ramkaka

SELECT

       I.progres_countryoforiginidname,

       I.progres_refugeestatusname,

          Count(DISTINCT CASE

                        WHEN progres_age <= 4 THEN progres_individualid

                        ELSE NULL  END) AS [ 1-5 ],

       Count(DISTINCT CASE

                        WHEN progres_age BETWEEN 5 AND 11 THEN progres_individualid

                                         ELSE NULL END) AS [ 5 - 10 ],

       Count(DISTINCT CASE

                        WHEN progres_age BETWEEN 12 AND 17 THEN progres_individualid

                        ELSE NULL END) AS [ 10 - 17 ],



       Count(distinct n.progres_individualname)

FROM

       Filteredprogres_specificneed N  Inner join Filteredprogres_individual I on N.progres_individualname = I.progres_indvidualid

WHERE

               N.progres_spnsubcategory2name  = 'Child at risk of not attending school'

               and n.statuscodename = 'Active'

               and n.progres_specificneedstatusname ='Valid'

              and I.StatusCodeName in('Active','hold')

              and (I.progres_refugeestatusname in('Asylum Seeker','Refugee') or I.progres_countryoforiginidname like '%syr%')



Group by I.progres_countryoforiginidname,I.progres_refugeestatusname

Order by      I.progres_countryoforiginidname ,count(distinct n.progres_individualname)  DESC