Yassine BHS Yassine BHS - 3 years ago 99
SQL Question

Return zero with Count and GroupBy

I have a mysql query with groupby and count but I cant have the lines where the count(*) gives 0.

code :

SELECT
e.sexe as sex, count(*) as number, n.id_niveau as niv

FROM
fact_resultat f, dim_etudiant e, dim_niveau n


WHERE
f.id_etudiant = e.id_etudiant AND f.id_niveau = n.id_niveau AND
resultat = 'Non Admis' AND etape = '1'
AND filiere = 'API'
GROUP BY sexe , niv


how can i make it return the lines with 0 count ?

Answer Source

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

One possible solution is to just use conditional aggregation:

SELECT e.sexe as sex, n.id_niveau as niv
       sum(resultat = 'Non Admis' AND etape = '1' AND filiere = 'API') as number,
FROM fact_resultat f JOIN
     dim_etudiant e
     ON f.id_etudiant = e.id_etudiant JOIN
     dim_niveau n 
     ON f.id_niveau = n.id_niveau
GROUP BY sexe, niv;

It is also possible that you need outer joins -- depending on what you mean by the rows with zero count.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download