Return all records with COUNT=0

Question

Here's an update of my query on SQL Fiddle

I want all records, even if the column nbDemandes is 0, for each nomLoi (RRQ and SAE).

So, the ACTUAL result :

nomTypeDemande | nomLoi | nbDemandes --------------- -------- ----------- Chef équipe RRQ 2 Mandat Projet SAE 4 PO RRQ 5 PO SAE 1

WANTED result :

nomTypeDemande | nomLoi | nbDemandes --------------- -------- ----------- Chef équipe RRQ 2 Chef équipe SAE 0 Mandat Projet RRQ 0 Mandat Projet SAE 4 PO RRQ 5 PO SAE 1

thanks a lot again to help me :)

Radu Gheorghiu · Accepted Answer · 2017-02-15 09:51:23Z

~~Replace your first INNER JOINs to a RIGHT JOIN / LEFT JOIN:~~

SELECT normesTypesDemande.choix AS nomTypeDemande, normesLois.choix AS nomLoi, COUNT(*) as nbDemandes FROM ((gestionDemandes.typeNormes RIGHT JOIN gestionDemandes.normesLois ON typeNormes.loi = normesLois.id) LEFT JOIN gestionDemandes.normesTypesDemande ON typeNormes.typeDemande = normesTypesDemande.id) LEFT JOIN gestionDemandes.demandes ON typeNormes.demande = demandes.id GROUP BY normesTypesDemande.choix, normesLois.choix ORDER BY normesTypesDemande.choix

Because of that INNER JOIN and no corresponding rows to count for Comité - SAE for example, the INNER JOIN will filter the SAE row.

~~Changing it to a RIGHT JOIN will ensure that no data from normesLois will be excluded, and you should have your 0 counts.~~

Update:

Well, not the most elegant, but here is the solution:

SELECT nomTypeDemande , choix , sum(nbDemandes) AS nbDemandes FROM ( SELECT r.nomTypeDemande , nl.choix , CASE WHEN r.nomLoi = nl.choix THEN sum(r.nbDemandes) ELSE 0 END AS nbDemandes FROM ( SELECT normesTypesDemande.choix AS nomTypeDemande , normesLois.choix AS nomLoi , COUNT(typeNormes.id) AS nbDemandes FROM normesLois FULL JOIN typeNormes ON typeNormes.loi = normesLois.id FULL JOIN normesTypesDemande ON typeNormes.typeDemande = normesTypesDemande.id GROUP BY normesTypesDemande.choix , normesLois.choix ) r CROSS JOIN normesLois nl GROUP BY r.nomTypeDemande , nl.choix , r.nomLoi ) r GROUP BY nomTypeDemande , choix ORDER BY nomTypeDemande , choix

Yes, I tried once again, but same result... Is there a away to add a field in the SELECT to catch count(0), for example?
@Patix80 Crazy enough I think (I'm not sure) you can use something like SUM(CASE WHEN typesNormes.id IS NULL THEN 0 ELSE 1 END) instead of COUNT(). But I'm surprised that replacing all inner joins with outer joins still doesn't return the data that you want.
Yes, that's surprised me too that RIGHT or LEFT JOIN doesn't make any change in records returned... Your idea with SUM(CASE WHEN typesNormes.id IS NULL THEN 0 ELSE 1 END) still works... like the COUNT

jean · Accepted Answer · 2017-02-14 14:59:32Z

As far I can guess you are counting demandés and categorizing. Problem is if there are no demand for a particular category they will not be present to be counted anyway.

The solution can be a left join on demandés, so all [normesLois] and [normesTypesDemande] will be returned, even if there are no demandés from that type.

SELECT normesTypesDemande.choix AS nomTypeDemande, normesLois.choix AS nomLoi, COUNT(typeNormes.id) as nbDemandes FROM gestionDemandes.typeNormes INNER JOIN gestionDemandes.normesLois ON typeNormes.loi = normesLois.id INNER JOIN gestionDemandes.normesTypesDemande ON typeNormes.typeDemande = normesTypesDemande.id LEFT JOIN gestionDemandes.demandes ON typeNormes.demande = demandes.id GROUP BY normesTypesDemande.choix, normesLois.choix ORDER BY normesTypesDemande.choix

PS: You don't really needs that extra parentesis on the joins

thanks for answer so fast and for PS :). But not difference for the records returned.
@Patix80 Maybe we are missing a detail here. If you post an verifiable example you can get a more assertive answers. Consider using SQLfiddle for example. Try again using only LEFT joins, maybe you are misssing a normesLois] and [normesTypesDemande] match

BSAMEI · Accepted Answer · 2017-02-14 15:06:11Z

You should change the inner to right join try the below query.

 SELECT T3.choix AS nomTypeDemande, T2.choix AS nomLoi, COUNT(T1.id) as nbDemandes FROM gestionDemandes.typeNormes T1 RIGHT JOIN gestionDemandes.normesLois T2 ON T1.loi = T2.id RIGHT JOIN gestionDemandes.normesTypesDemande T3 ON T1.typeDemande = T3.id INNER JOIN gestionDemandes.demandes T4 ON T1.demande = T4.id GROUP BY T3.choix, T2.choix ORDER BY T3.choix

I tried your request, but the wanted result's wasn't what it should be...

Collectives™ on Stack Overflow

Return all records with COUNT=0

3 Answers 3

12 Comments

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

2 Comments

1 Comment

Related