Discussion :

[olibara]

Bonjour

J'essaye de trouver la maniere de faire un count sur un left join
C'est a dire donner le nombre d'éléments effectivement lié par le join

Ci-apres exemple du query :
le group by addrid me permet de faire un sum de poids par adresse
Mais j'aimerais aussi avoir le count des containers par adresse, sachant que certaines adresse n'ont pas de containers et que d'autres en ont plusieurs

Merci pour votre aide
Je ne suis qu'un amateur en SQL.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT
address.addrID AS Adr,
address.Plaats,
Sum(ritstat.gewicht) AS Gewicht,
address.archive AS Weg,
-- containers.Scan_Num
FROM
address
Inner Join ritstat ON address.addrID = ritstat.addrID
Left Outer Join containers ON address.addrID = containers.addrID
where CODE=38025 and address.addrid<7000 
 and ritstat.date>=20090408 
 and ritstat.date<=20100409
GROUP BY ritstat.addrID
order by plaats,address.addrID

[CinePhil]

faire un count sur un left join
C'est a dire donner le nombre d'éléments effectivement lié par le join

Ci-apres exemple du query :
le group by addrid me permet de faire un sum de poids par adresse
Mais j'aimerais aussi avoir le count des containers par adresse, sachant que certaines adresse n'ont pas de containers et que d'autres en ont plusieurs

Quel COUNT avez-vous essayé ?

Quelques remarques sur votre requête :
1) Vous devriez préfixer toutes les colonnes afin qu'on sache de quelle table elles viennent. Et utiliser des alias de tables rend la requête plus lisible.
2) Les dates devraient être dans des colonnes de type DATE au format 'aaaa-mm-jj' et non pas dans des colonnes numériques.
3) Il existe la fonction BETWEEN pour comparer une date avec un intervalle de temps.
4) Le GROUP BY devrait comprendre toutes les colonnes du SELECT ne faisant pas l'objet d'un calcul.

Votre requête réécrite avec une suggestion pour le COUNT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
  a.addrID AS Adr,
  a.Plaats,
  Sum(r.gewicht) AS Gewicht,
  a.archive AS Weg,
-- containers.Scan_Num
  COUNT(c.addrID) AS Container_zahl
FROM address AS a
INNER JOIN ritstat AS r ON a.addrID = r.addrID
LEFT OUTER JOIN containers AS c ON a.addrID = c.addrID
WHERE CODE = 38025 -- De quelle table vient CODE ?
    AND a.addrid < 7000 
  AND r.date BETWEEN '2009-04-08' AND '2010-04-09'
GROUP BY a.addrID, a.Plaats, a.archive
ORDER BY a.plaats, a.addrID

[olibara]

Merci Cinephil

Toutes tes remarques sont tres pertientes et je vais essayer d'en tenir compte

Pour le group by, j'use et j'abuse d'une facilité de MySql qui permet d'ignorer certains champs

Pour le reste, j'essaye des que possible aujourd'hui

[olibara]

Bonjour

J'ai essayé la proposition de Cinephil
Mais je réalise que le probleme c'est que le count que j'obtiens n'est pas le count des container physiquement a l'adresse mais la multiplication de ceux ci par le nombre de passage a l'adresse durant la periode concernée

Cette multiplication est due pas le join sur la table ritstat qui contient le relevé des passage a l'adresse

La seule solution que j'entrevois selon mes compétence est de construire une table temporaire

ca donnerait ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
drop table if exists tempo;
create temporary table tempo
select 
count(*) as cnt,
addrid
from containers
group by addrid;
SELECT
  a.addrID AS Adr,
  a.Plaats,
  Sum(r.gewicht) AS Gewicht,
  a.archive AS Weg,
-- containers.Scan_Num
  t.cnt
FROM address AS a
INNER JOIN ritstat AS r ON a.addrID = r.addrID
LEFT OUTER JOIN tempo AS t ON a.addrID = t.addrID
WHERE a.NIS = 38025 -- De quelle table vient CODE ?
    AND a.addrid < 7000 
  AND r.date BETWEEN '2009-04-08' AND '2010-04-09'
GROUP BY a.addrID, a.Plaats, a.archive
ORDER BY a.plaats, a.addrID

[CinePhil]

le count que j'obtiens n'est pas le count des container physiquement a l'adresse mais la multiplication de ceux ci par le nombre de passage a l'adresse durant la periode concernée

Essaie ma requête avec un COUNT(DISTINCT c.addrID).

[olibara]

Essaie ma requête avec un COUNT(DISTINCT c.addrID).

Merci Cinephil

Mais en toute logique, sans meme essayer je dirais que ce ne marchera pas
Car une adresse possedant trois containers distinct sur Scan_Num ne sera pas distinct sur addrid

je pourrais donc essayer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

  COUNT(DISTINCT c.AddrId,c.Scan_Num) AS Container_zahl

En tout cas ma table temporaire fonctionne et je ne suis pas certain que ce soit moins efficace

[olibara]

Bonsoir Cinephil

Je voulais te dire que ta proposition avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 COUNT(DISTINCT c.Scan_Num) AS Container_zahl

Marche tres bien
Merci beaucoup !

[olibara]

Bonjour

Je reviens sur le sujet car je viens de me faire allumer !!
La solution du Count Distinct ne fonctionne pas comme il faut !!
Pour des raisons obscure le Sum() donne des resultat tout a fait surévalués

Finalement c'est ma solution de table temporaire qui donne le bon résultat

- Elle est un peu plus lente
- Elle est sans doute moins académique

Mais elle a l'avantage d'etre asser explicite a la lecture
Et de donner le bon resultat

Ma trop maigre expérience de SQL ne me permet hélas pas de comprendre au premier (ni au second) coup d'oeil ce qui foire dans la proposition de Cinephil

Voici ma solution

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop table if exists tempo;
create temporary table tempo
select 
count(*) as Bak,
addrid
from containers
group by addrid;
SELECT
  a.addrID AS Adr,
  a.Plaats,
  Sum(r.gewicht) AS Gewicht,
  a.archive AS Weg,
  t.Bak
FROM address AS a
INNER JOIN ritstat AS r ON a.addrID = r.addrID
LEFT OUTER JOIN tempo AS t ON a.addrID = t.addrID
WHERE a.NIS = 12025 
  AND a.addrid < 7000 
  AND r.date BETWEEN '2009-04-08' AND '2010-04-09'
GROUP BY a.addrID, a.Plaats, a.archive
ORDER BY a.plaats, a.addrID

[olibara]

N.B.

A l'analyse le sum donné dans la solution de Cinephil est multiplié par le resultat du count Count !!