Quelles clause pour compter le nombre occurrences dans une colonne à partir d'une ligne

**oneagaindoguys** · 27/09/2017, 15h51

Bonjour,

Désolé si le titre n'est pas clair...
Admettons une table toute simple : ID, LIBELLE, PROVENANCE. Dans cette table, je souhaite extraire l'id, le libellé et le nombre de ligne où provenance = id.

Par exemple :

Code :

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

1
2
3
4
5
1, toto, -1
2, titi, -1,
3, tutu, 1
4, tata, 1
5, toutou, 2

Je souhaite obtenir quelques chose comme

Code :

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

1
2
3
4
5
1, toto, 2
2, titi, 1,
3, tutu, 0
4, tata, 0
5, toutou, 0

Dans mon cas précis, j'ai beaucoup plus de colonnes qui sont déjà issues d'une jointure de plusieurs tables.

J'ai essayé de faire

Code :

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

1
2
3
4
5
 
SELECT id, libelle, droite.nb
FROM
   (SELECT id, libelle, provenance FROM Table) AS gauche,
   (SELECT COUNT(*) AS nb, provenance FROM Table GROUP BY provenance) AS droite

Initialement, je le faisais en 2 requête mais le but serait de trier par nombre de provenance

Merci d'avance pour votre aide

**SQLpro** · 27/09/2017, 16h04

Code :

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

1
2
SELECT id, libelle, droite.nb,  COUNT(*) OVER(PARTITION BY provenance) AS nb
FROM ...

A +

**tatayo** · 27/09/2017, 16h05

Bonjour,
Je dirai un truc du genre:

Code sql :

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

1
2
3
4
 
select id,libelle,sum(case when provenance = id then 1 else 0 end)
from latable
group by id,libelle

Tatayo.

**Waldar** · 27/09/2017, 16h12

Une solution scalaire, acceptable avec un index sur provenance et pas trop de données :

Code :

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

1
2
3
4
  select id, libelle
       , (select count(*) from MaTable as t2 where t2.provenance = t1.id) as nb
    from MaTable as t1
order by id asc;

Une solution avec jointure, préférable si vous n'avez pas d'index sur provenance ou beaucoup de données :

Code :

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

1
2
3
4
5
6
7
8
9
10
with cte_agg (provenance, nb) as
(
  select provenance, COUNT(*)
    from MaTable
group by provenance
)
    select t1.id, t1.libelle, coalesce(t2.nb, 0) as nb
      from MaTable as t1
 left join cte_agg as t2 on t2.provenance = t1.id
  order by t1.id asc;

**oneagaindoguys** · 27/09/2017, 16h44

Envoyé par SQLpro

Code :

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

1
2
SELECT id, libelle, droite.nb,  COUNT(*) OVER(PARTITION BY provenance) AS nb
FROM ...

A +

Merci. Mais...

Ca me produit :

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
1, toto, 0
1, toto, 1
1, toto, 2
2, titi, 0,
2, titi, 1,
2, titi, 2,
3, tutu, 0
3, tutu, 1
3, tutu, 0
4, tata, 0
4, tata, 1
4, tata, 2
5, toutou, 0
5, toutou, 1
5, toutou, 2

au lieu de

Code :

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

1
2
3
4
5
1, toto, 2
2, titi, 1,
3, tutu, 0
4, tata, 0
5, toutou, 0

**oneagaindoguys** · 27/09/2017, 16h45

Envoyé par tatayo

Bonjour,
Je dirai un truc du genre:

Code sql :

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

1
2
3
4
 
select id,libelle,sum(case when provenance = id then 1 else 0 end)
from latable
group by id,libelle

Tatayo.

Merci. Mais...
Je n'ai pas seulement 1 ou 0...

**oneagaindoguys** · 27/09/2017, 16h51

Envoyé par Waldar

Une solution scalaire, acceptable avec un index sur provenance et pas trop de données :

Code :

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

1
2
3
4
  select id, libelle
       , (select count(*) from MaTable as t2 where t2.provenance = t1.id) as nb
    from MaTable as t1
order by id asc;

Je vais me contenter de ça. Merci beaucoup

**tatayo** · 27/09/2017, 17h49

Envoyé par oneagaindoguys

Merci. Mais...
Je n'ai pas seulement 1 ou 0...

Oui, mais... les 1 et 0 permettent juste de compter les lignes "qui vont bien".
Je regroupe les lignes sur l'Id et le libelle, et je fais la somme de (si provenance = id alors 1 sinon 0).
Pour moi cette requête répond à la question posée.
Il suffit d'enlever le SUM et le GROUP BY, de lire le résultat et de faire le calcul à la main pour s'en convaincre.

Tatayo.