Wing5wong Wing5wong - 5 months ago 12
SQL Question

Group and count from joined tables

I am trying to get a row per learner, with the following format:

nsn|last_name|first_name|middle_name|gender|ethnicities|providers|qualifications|vocation_PI|vocation_SI|.....


a Learner has many providers, provider has many learners
at each provider, a learner has many results, results belong to one learner
a result has one standard, a standard belongs to none/many results
a standard has many vocations

select l.nsn, l.last_name,l.first_name,l.middle_name, l.gender, GROUP_CONCAT(distinct e.name SEPARATOR '|') as ethnicities, GROUP_CONCAT(distinct p.name SEPARATOR '|') as providers, count(case v.code when 'PI' then 1 else 0 end) as v_PI,count(case v.code when 'SI' then 1 else 0 end) as v_SI,count(case v.code when 'CR' then 1 else 0 end) as v_CR, GROUP_CONCAT(distinct q.name SEPARATOR '|') as qualifications
from learners l
join learner_ethnicity le on l.id = le.learner_id
join ethnicities e on le.ethnicity_id = e.id
join learner_provider lp on l.id = lp.learner_id
join providers p on lp.provider_id = p.id
join results r on lp.id = r.learner_provider_id
join standards s on r.standard_id = s.id
join standard_vocation sv on s.id = sv.standard_id
join vocations v on sv.vocation_id = v.id
join learner_qualification lq on l.id = lq.learner_id
join qualifications q on lq.qualification_id = q.id
group by l.id


I cant get the count for each vocation to work; i get the same value in each count field (not sure if this is a total of all counts or what)

expected output:

123451234 | Doe | John | James | M | European|Maori | SecondaryProvider|TertiaryProvider | NCEA Level 1|NCEA Level 2|Farming PHD | 185 | 430 | .....


actual output:

123451234 | Doe | John | James | M | European|Maori | SecondaryProvider|TertiaryProvider | NCEA Level 1|NCEA Level 2|Farming PHD | 500 | 500 | .....


Any suggestions?

Answer

REPLACE YOUR COUNT WITH SUM YOU WILL GET WHAT YOU WANT

select l.nsn, l.last_name,l.first_name,l.middle_name, l.gender, GROUP_CONCAT(distinct e.name SEPARATOR '|') as ethnicities, GROUP_CONCAT(distinct p.name SEPARATOR '|') as providers, sum(case v.code when 'PI' then 1 else 0 end) as v_PI, sum(case v.code when 'SI' then 1 else 0 end) as v_SI,sum(case v.code when 'CR' then 1 else 0 end) as v_CR, GROUP_CONCAT(distinct q.name SEPARATOR '|') as qualifications
from learners l 
join learner_ethnicity le on l.id = le.learner_id
join ethnicities e on le.ethnicity_id = e.id
join learner_provider lp on l.id = lp.learner_id
join providers p on lp.provider_id = p.id
join results r on lp.id = r.learner_provider_id
join standards s on r.standard_id = s.id
join standard_vocation sv on s.id = sv.standard_id
join vocations v on sv.vocation_id = v.id
join learner_qualification lq on l.id = lq.learner_id
join qualifications q on lq.qualification_id = q.id
group by l.id