Discussion :

[laurentSc]

Bonjour,

j'essaie de me remettre au SQL ; j'avais compris, il y a environ 6 mois, à partir d'un exemple qu'on m'avait décortiqué ; mais j'essaie de m'y replonger maintenant et je bloque ; je crois que ce qui me gêne, c'est la clause "group by". Qqun peut-il me l'expliquer ? Les exemples, ça marche bien, mais sans bdd (et via le forum c'est difficile) je ne voie pas...

[Morphorpse]

Bonsoir,

La clause Group by est utilisé pour, comme son nom l'indique regrouper des résultats.

Par exemple tu a des articles dans un rayon et plusieurs rayons dans un magasin.

Tu connais la quantité de chaque article vendu uniquement.

Si tu veux regrouper par rayon par exemple tu utiliseras group by
Ca donneras quelquechose comme cela

select count(article.quantite) from article , rayon
where article.referencerayon=rayon.identifiant
group by rayon

Tu aurais donc

Rayon 1 : 200 articles
Rayon 2 : 24 articles
....

Alors que sans group by
tu aurais

Rayon 1 : Article 1 : 50 articles
Rayon 1 : Article 2: 150 articles

J'espère etre clair

Bon courage

[AL1986]

En gros quand tu fais un

Code SQL :

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

group by ListeDesChamps

tu obtiendras des ensembles de tuples tels que pour chaque ensemble tous les tuples ont les mêmes valeurs des champs se trouvant dans ListeDesChamps.
Si t'as la table suivante :

Code :

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

1
2
3
4
5
6
7
8
Hotel station clients
1     1       50
2     1       100
1     1       123
3     2       43
2     1       345
3     3       231
2     2       20

Un

Code SQL :

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

1
2
3
select hotel, station, sum(clients)
from latable
group by hotel, station;

créera les groupes suivants

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Hotel Station clients
1     1       50
1     1       123
-------------------
2     1       100
2     1       345
-------------------
3     2       43
-------------------
3     3       231
-------------------
2     2       20

et la requête se fera sur chacun des groupes, ton résultat sera donc

Code :

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

1
2
3
4
5
6
hotel station sum(clients)
1     1       173
2     1       445
3     2       43
3     3       231
2     2       20

Si tu n'avais pas fait de group by le résultat serait

Code :

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

1
2
3
4
5
6
7
8
Hotel station sum(clients)
1     1       912
2     1       912
1     1       912
3     2       912
2     1       912
3     3       912
2     2       912

Note que tous les champs de ton select doivent être présents dans le goup by (qui peut en contenir plus) sauf les champs utilisés dans des fonctions d'agrégats (dans l'exemple le champ clients n'est pas utilisé dans le goup by car utilisé avec sum).

[laurentSc]

Oui, c'est clair, d'autant plus qu'entre tps, la "mémoire" m'est revenue. Merci pour ton explication.

[laurentSc]

Je réponds à AL1986. Ta réponse sur les champs de select qui doivent appartenir à la clause "group by" (sauf ceux appartenant à une fct d'agrégat) me surprend car 1/ j'ai sous les yeux des requêtes (ds un code php qui interroge en mysql une base) qui ne respectent pas cette règle et pourtant marchent 2/ on mets parfois des champs dont le seul but est de rendre la table plus compréhensible 3/ si on applique ta règle, ça modifie le résultat. Cela dit, je viens de faire une requête simplicissime qui respecte cette règle et qui marche (que demande le peuple ?). En tout cas, merci pour la réponse.

[AL1986]

Je réponds à AL1986. Ta réponse sur les champs de select qui doivent appartenir à la clause "group by" (sauf ceux appartenant à une fct d'agrégat) me surprend car 1/ j'ai sous les yeux des requêtes (ds un code php qui interroge en mysql une base) qui ne respectent pas cette règle et pourtant marchent 2/ on mets parfois des champs dont le seul but est de rendre la table plus compréhensible 3/ si on applique ta règle, ça modifie le résultat. Cela dit, je viens de faire une requête simplicissime qui respecte cette règle et qui marche (que demande le peuple ?). En tout cas, merci pour la réponse.

En ce qui concerne les agrégats, je maintiens toujours, puisque ça ne servirait à rien de les mettre dans le goup by. Par exemple pour le sum de mon exemple, si je mets clients dans le group by j'aurais en résultat la table en entrée.
La structure général d'une requête avec un group by serait

Code SQL :

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

1
2
3
select ch1, ch2, ch3
from table1
group by ch1, ch2, ch3 [, ch4, ch5, ....];

N'ayant travaillé que sur ORACLE, je ne sais pas si c'est une norme SQL ou ORACLE
Pourrais-tu poster les bouts de code auxquels tu fais allusion ? Merci

[laurentSc]

Souhaites-tu le code php (je n'en voie pas l'intérêt) ou la requête SQL ?
Voici une des requêtes (pas piquée des vers) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 select URLs.URL as URL
 , titre, URLs.description as descriptionURL
 , Sports.id as idsport
 , Sports.nom as nomsport
 , count(URLs.id) as nb 
from URLs 
 , Sports
 , URLSport 
where URLSport.sport=Sports.id 
 and URLSport.url=URLs.id 
group by Sports.id 
order by Sports.nom ASC

[al1_24]

Cette requête ne fonctionnera qu'avec MySQL qui n'est pas connu pour son respect de la norme SQL.
Je t'encourage par ailleurs à lire ce Cours sur les Regroupements en SQL

[jbeu]

Bonjour AL1986, Laurent, et les futurs autres lecteurs

Cette question m'intéresse beaucoup aussi parce que j'ai une énorme instruction à concatener, et si je dois recopier les champs du select dans le group by, ca me complique (légèrement) la chose.

Le cas des champs sur lesquels s'appliquent les agrégats est bien sûr réglés, je suis d'accord avec vous.

Par contre, qu'en est-il des autres champs ? Et j'ajoute une composante à votre question : qu'en est-il des champs d'autres tables ?

Pour apporter mon témoignage : effectivement, dans Access, si on n'ajoute pas les champs dans le group by, la requete ne retourne aucun résultat.
Y aurait-il des cas particuliers à prendre en compte, qui expliqueraient nos différences avec LaurentSC ?

Après moults recherches, je n'ai pas trouvé d'autres discussions à ce sujet sur les forums.

Merci à vous.

[laurentSc]

jbeu,

je veux bien t'aider, mais je précise que je ne suis pas du tout spécialiste (ce matin, je croyais encore être incapable de faire des requêtes SQL), et de plus, je ne comprends pas ton besoin (Peut-être que AL1986 peut t'aider plus facilement...)

[AL1986]

Par contre, qu'en est-il des autres champs ? Et j'ajoute une composante à votre question : qu'en est-il des champs d'autres tables ?

Pour respecter la norme SQL il faut que la clause group by contienne au moins tous les champs du select qui lui est associée (champs utilisés dans les fonctions d'agrégats exclus) quelque soit le nombre de tables mises en jeu
Exemples :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
select t1.ch1, t2.ch2, t3.ch3
from table1 t1, table2 t2, table3 t3
group by t1.ch1, t2.ch2, t3.ch3 [, autres champs si necessaire];
 
 
select t1.ch1, t2.ch2, t3.ch3
from table1 t1, table2 t2, table3 t3
where exists (select t4.ch4, t5.ch5
              from table4 t4, table5 t5
              group by t4.ch4, t5.ch5 [, autres champs appartenant aux tables t4 et t5 si necessaire])
group by t1.ch1, t2.ch2, t3.ch3 [, autres champs appartenant aux tables t1, t2 et t3 si necessaire];

En espérant répondre à tes questions. Sinon n'hésite pas à demander.

[jbeu]

Merci pour vos réponses.
Mes essais m'ont encouragé à continuer, mais je me heurte à ce problème.
(j'espère que ce n'est pas ce que tu sous-entendais avec le Where exists, sinon je passe pour un con! Et je te demanderais dans ce cas de bien vouloir m'explique son fonctionnment...).

Je réussis bien à gérer un

Code :

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

SUM(truc) Group By table1.id

ou un

Code :

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

MIN(machin) Group BY table2.id

mais je ne sais pas gérer les 2 en même temps.

Je voudrais faire 2 agrégats sur 2 tables différentes.

C'est à dire que dans ma table1, j'ai un "niveau" pour chaque ligne (avec des lignes ayant en commun un "dossier")
et dans ma table2, j'ai une "quantité" pour chaque ligne (avec des lignes ayant en commun un "dossier").
Je voudrais afficher dans mes résultats :
dossier1 | MIN(niveau) | SUM(quantité)

Si j'arrivais à afficher ces 2 résultats d'agrégats par la même requete, je serai Heureux !

Merci d'y contribuer !

Faites moi signe si vous voulez des vrais bouts de code.

[al1_24]

Il faut passer alors par des tables dérivées :

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    t1.id    
    ,    t1.niv
    ,    t2.qte
FROM    (    SELECT    id
                ,    MIN(machin)    AS niv
            FROM    table1
            GROUP BY id
        )    AS t1
    INNER JOIN
        (    SELECT    id
                ,    SUM(truc)    AS qte
            FROM    table2  /* Avant correction : FROM table1 */
            GROUP BY id
        )    AS t2  /* Avant correction :  AS t1 */
        ON    t1.id = t2.id
;

[laurentSc]

Je suis impressionné mais complètement largué. Je ne compte pas atteindre ce niveau (qui semble très élevé).

[jbeu]

Waouh ! la classe !
Je pense avoir compris le principe, qui correspond tout à fait à ce que je cherchais.
Et ça marche !

En plus, comme je suis en train de concaténer une grande instruction SQL en VBA (construction pas à pas par l'utilisateur), je peux même créer des strings séparés qui correspondent aux tables dérivées et les intégrer facilement au bon moment.

Donc un immense MERCI !

Juste un détail pour les futurs codeurs qui passeront par ici :
Je crois qu'il y a une erreur de copier/coller dans la 2eme table dérivée, qui devrait s'appeler () AS t2.

mes salutations !

[al1_24]

Juste un détail pour les futurs codeurs qui passeront par ici :
Je crois qu'il y a une erreur de copier/coller dans la 2eme table dérivée, qui devrait s'appeler () AS t2.

Bien vu

[AL1986]

Il faut passer alors par des tables dérivées :

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    t1.id    
    ,    t1.niv
    ,    t2.qte
FROM    (    SELECT    id
                ,    MIN(machin)    AS niv
            FROM    table1
            GROUP BY id
        )    AS t1
    INNER JOIN
        (    SELECT    id
                ,    SUM(truc)    AS qte
            FROM    table1
            GROUP BY id
        )    AS t1
        ON    t1.id = t2.id
;

Pourrait-on m'expliquer la différence entre la requête de al1_24 et cette requête ? merci

Code SQL :

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

1
2
3
SELECT t1.id, MIN(t1.machin), SUM(t1.truc)
FROM table1 t1
GROUP BY t1.id;

[jbeu]

Je pense que ta question est justifiée, mais en fait je crois que c'est juste du à une erreur de copier/coller (décidemment )
Il faudrait plutot lire :

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    t1.id    
    ,    t1.niv
    ,    t2.qte
FROM    (    SELECT    id
                ,    MIN(machin)    AS niv
            FROM    table1
            GROUP BY id
        )    AS t1
    INNER JOIN
        (    SELECT    id
                ,    SUM(truc)    AS qte
            FROM    table2
            GROUP BY id
        )    AS t2
        ON    t1.id = t2.id
;

Une fois ces petites corrections effectuées, expliquons les différences entre la requete avec tables dérivées d'al1_24 et ta requete AL1986.

La plus simple (AL1986) consiste à créer 2 agrégats avec des données d'une seule table. Dans tous les cas, il n'existe qu'une table.

Dans la requete avec tables dérivées, il existe au départ 2 tables différentes (table1 et table2). Nous en créons 2 nouvelles (t1 et t2) et effectuons une jointure sur t1 et t2.
L'avantage est alors de créer des agrégats sur un meme champ (id ou no_dossier), mais avec des données de 2 tables. (ce que je cherchais)

Al1_24, ai-je bien compris ta leçon ?

NB : on peut aussi bien sûr ajouter des champs, même d'autres tables, en ajoutant donc le nom des tables appelées (bien les sortir des parenthèses), et en créant la liaison avec la table principale (WHERE table1.couleur=table4.couleur ).
Un autre point intéressant : avec cette technique, il n'est pas nécessaire de recopier dans la clause groupby générale tous les champs qu'on aura ajoutés dans le select (puisque les GroupBy ne sont que des petits, dans les requetes imbriquées). (cf le problème du début de cette discussion)

J'espère que ca s'est éclairci.
Sinon à bientôt !

[al1_24]

A la demande générale, j'ai effectué les corrections dans ma requête

[AL1986]

Je pense que ta question est justifiée, mais en fait je crois que c'est juste du à une erreur de copier/coller (décidemment )
Il faudrait plutot lire :

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    t1.id    
    ,    t1.niv
    ,    t2.qte
FROM    (    SELECT    id
                ,    MIN(machin)    AS niv
            FROM    table1
            GROUP BY id
        )    AS t1
    INNER JOIN
        (    SELECT    id
                ,    SUM(truc)    AS qte
            FROM    table2
            GROUP BY id
        )    AS t2
        ON    t1.id = t2.id
;

Une fois ces petites corrections effectuées, expliquons les différences entre la requete avec tables dérivées d'al1_24 et ta requete AL1986.

La plus simple (AL1986) consiste à créer 2 agrégats avec des données d'une seule table. Dans tous les cas, il n'existe qu'une table.

Dans la requete avec tables dérivées, il existe au départ 2 tables différentes (table1 et table2). Nous en créons 2 nouvelles (t1 et t2) et effectuons une jointure sur t1 et t2.
L'avantage est alors de créer des agrégats sur un meme champ (id ou no_dossier), mais avec des données de 2 tables. (ce que je cherchais)

Al1_24, ai-je bien compris ta leçon ?

NB : on peut aussi bien sûr ajouter des champs, même d'autres tables, en ajoutant donc le nom des tables appelées (bien les sortir des parenthèses), et en créant la liaison avec la table principale (WHERE table1.couleur=table4.couleur ).
Un autre point intéressant : avec cette technique, il n'est pas nécessaire de recopier dans la clause groupby générale tous les champs qu'on aura ajoutés dans le select (puisque les GroupBy ne sont que des petits, dans les requetes imbriquées). (cf le problème du début de cette discussion)

J'espère que ca s'est éclairci.
Sinon à bientôt !

Et par rapport à la vitesse d'exécution ?