[Access] Trouver qui a le plus grand nombre de visites

**maxidoh** · 15/03/2006, 12h27

comment résoudre une requête du genre:
le médecin ayant reçu le plus de patients avec le modèle relationnel suivant:

Patient(NumP,NomP,PrénomP,TélP,NumC)
Médecin(NumM,NomM,PrénomM,AdrM,VilleM,TélM)
Consulter(NumP,NumM,Date,Tarif,RembSécu,RembMut)
Caisse(NumC,NomC,AdrC,VilleC,TélC)

Xo · 15/03/2006, 15h23

Salut, et bienvenue sur ce forum,

La requête suivante te renvoie l'identifiant de chaque médecin avec le nombre de visites :

Code :

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

1
2
3
SELECT numM, Count(*)
  FROM Consulter
 GROUP BY numM;

Tu trouveras le nombre max de visites en procédant ainsi :

Code :

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

1
2
3
SELECT Max(Count(*))
  FROM Consulter
 GROUP BY numM;

Donc la requête cherchée peut s'écrire :

Code :

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

1
2
3
4
5
6
SELECT numM, Count(*)
  FROM Consulter
 GROUP BY numM
 HAVING Count(*) = (SELECT Max(Count(*))
                      FROM Consulter
                     GROUP BY numM);

**maxidoh** · 17/03/2006, 11h12

désolé,ça ne marche pas.Avec access j'ai l'erreur "impossible d'avoir une fonction d'agrégat max(count(*)).J'ai pu quand même trouver la solution :

Code :

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

1
2
3
4
5
SELECT TOP 1 Médecin.PrénomM, Médecin.NomM
  FROM Médecin, Consulter
 WHERE Médecin.NumM=Consulter.NumM
 GROUP BY Médecin.NomM, Médecin.PrénomM
 ORDER BY Count(*) DESC;

Xo · 17/03/2006, 11h26

D'où l'utilité de préciser le SGBD utilisé dès la demande initiale.

Merci de nous avoir fait part de ta solution

Et merci de penser au tag Résolu

**Yanika_bzh** · 17/03/2006, 13h10

Je profite de cette petite porte pour demander quelle est la signification d'un Group By utilisé sans fonction d'aggrégation ???

**Médiat** · 17/03/2006, 13h42

C'est équivalent d'un DISTINCT.

**Magnus** · 17/03/2006, 14h00

Xo >> je comprends ta solution et en essayant d'écrire une solution de mon côté je bloquais car j'effectuais cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
8i CYRIL> l
  1  SELECT numM, Max(Count(*))
  2    FROM Consulter
  3*  GROUP BY numM
8i CYRIL> /
SELECT numM, Max(Count(*))
       *
ERROR à la ligne 1 :
ORA-00937: not a single-group group function

Comme tu le vois le fait d'ajouter les colonnes du GROUP BY dans le SELECT provoque un message d'erreur.

As-tu une idée pourquoi ?

**Yanika_bzh** · 17/03/2006, 15h18

Envoyé par Médiat

C'est équivalent d'un DISTINCT.

Ok c'est l'equivalent d'un distinct dans le resultat, mais que fait le SGBD exactement ? quel est la signification effective d'un group by sans commande d'aggrégation. Pour moi demander un groupement sur un ensemble non groupable, j'ai du mal a saisir ...

**Magnus** · 17/03/2006, 15h44

Envoyé par Yanika_bzh

Envoyé par Médiat

C'est équivalent d'un DISTINCT.

Ok c'est l'equivalent d'un distinct dans le resultat, mais que fait le SGBD exactement ? quel est la signification effective d'un group by sans commande d'aggrégation. Pour moi demander un groupement sur un ensemble non groupable, j'ai du mal a saisir ...

C'est simple : imagine que tu gères une société avec des clients et des commandes.
Il y a de forte probabilités pour que tu aies une table que je nomme qui associe les 2 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
8i CYRIL> select *
  2  from dvp;
 
    CLIENT   COMMANDE
---------- ----------
        11       1005
        11       3033
        11     102035
        56          1
        56          8
 
5 ligne(s) sélectionnée(s).

Tu veux connaître tous les clients (distincts) ayant passé au moins une commande :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
8i CYRIL> l
  1  select *
  2* from dvp
8i CYRIL> /
 
    CLIENT   COMMANDE
---------- ----------
        11       1005
        11       3033
        11     102035
        56          1
        56          8
 
5 ligne(s) sélectionnée(s).
 
8i CYRIL> select client
  2  from dvp
  3  group by client;
 
    CLIENT
----------
        11
        56
 
2 ligne(s) sélectionnée(s).
 
8i CYRIL> select distinct client
  2  from dvp;
 
    CLIENT
----------
        11
        56
 
2 ligne(s) sélectionnée(s).

C'est plus clair maintenant ?

Xo · 17/03/2006, 16h47

Envoyé par Magnus

Xo >> je comprends ta solution et en essayant d'écrire une solution de mon côté je bloquais car j'effectuais cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
8i CYRIL> l
  1  SELECT numM, Max(Count(*))
  2    FROM Consulter
  3*  GROUP BY numM
8i CYRIL> /
SELECT numM, Max(Count(*))
       *
ERROR à la ligne 1 :
ORA-00937: not a single-group group function

Comme tu le vois le fait d'ajouter les colonnes du GROUP BY dans le SELECT provoque un message d'erreur.

As-tu une idée pourquoi ?

Les raisons profondes, je ne sais pas, je ne suis pas un expert en SQL et j'ai été confronté au même souci que toi

, d'où ma solution avec sous-requête.
La "double" application de fonctions d'aggrégation doit poser problème ...

**Yanika_bzh** · 18/03/2006, 00h58

Envoyé par Magnus

Envoyé par Yanika_bzh

Envoyé par Médiat

C'est équivalent d'un DISTINCT.

Ok c'est l'equivalent d'un distinct dans le resultat, mais que fait le SGBD exactement ? quel est la signification effective d'un group by sans commande d'aggrégation. Pour moi demander un groupement sur un ensemble non groupable, j'ai du mal a saisir ...

C'est simple : imagine que tu gères une société avec des clients et des commandes.
Il y a de forte probabilités pour que tu aies une table que je nomme qui associe les 2 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
8i CYRIL> select *
  2  from dvp;
 
    CLIENT   COMMANDE
---------- ----------
        11       1005
        11       3033
        11     102035
        56          1
        56          8
 
5 ligne(s) sélectionnée(s).

Tu veux connaître tous les clients (distincts) ayant passé au moins une commande :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
8i CYRIL> l
  1  select *
  2* from dvp
8i CYRIL> /
 
    CLIENT   COMMANDE
---------- ----------
        11       1005
        11       3033
        11     102035
        56          1
        56          8
 
5 ligne(s) sélectionnée(s).
 
8i CYRIL> select client
  2  from dvp
  3  group by client;
 
    CLIENT
----------
        11
        56
 
2 ligne(s) sélectionnée(s).
 
8i CYRIL> select distinct client
  2  from dvp;
 
    CLIENT
----------
        11
        56
 
2 ligne(s) sélectionnée(s).

C'est plus clair maintenant ?

Ok, je sais ce qu'est un select distinct

J'ai compris que tu voulais m'expliquer que le resultat obtenu est le meme qu'un select distinct.
Moi je te parle de la philosophie d'un SGBD.
Pour toi, quelle est la signification d'un select champ1 from Table1 group by champ1 ? Quand tu tapes cette commande, comment imagines tu le fonctionnement interne de ton SGBD ?

Tiens et tant qu'on y est

Dans ton exemple tu as une table associative. Sachant que la relation liant commande a Client est de type 1,1 (en effet une commande n'appartient qu'a un et un seul client), la nième forme normale de Merise n'indique pas le fait que tu n'aies pas besoin d'utiliser cette table associative ?

Tant de questions que je me pose ...

Finirai je une seule journée sans me poser de question ??

En tout cas Magnus, merci pour cet echange tres interessant et je l'espere pas uniquement pour moi

**Médiat** · 18/03/2006, 05h48

Envoyé par Magnus

Xo >> je comprends ta solution et en essayant d'écrire une solution de mon côté je bloquais car j'effectuais cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
8i CYRIL> l
  1  SELECT numM, Max(Count(*))
  2    FROM Consulter
  3*  GROUP BY numM
8i CYRIL> /
SELECT numM, Max(Count(*))
       *
ERROR à la ligne 1 :
ORA-00937: not a single-group group function

Comme tu le vois le fait d'ajouter les colonnes du GROUP BY dans le SELECT provoque un message d'erreur.

As-tu une idée pourquoi ?

Le COUNT(*) est calculé sur les groupes définis par la clause GROUP BY, ici, pour chaque numM (donc le numM de chaque groupe existe et est parfaitement défini), mais le MAX (deuxième niveau d'agrégation) porte sur l'ensemble de la table virtuelle générée par le GROUP BY, il n'est donc pas possible de sélectionner une colonne.

EX :

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
SELECT numM, COUNT(*)
FROM Consulter
GROUP BY numM
 
numM  COUNT
1       3
2       7
3       1
 
si j'ajoute le MAX, il va porter sur la table générée par le résultat précédent
 
SELECT MAX(COUNT(*))
FROM Consulter
GROUP BY numM
 
MAX
7

Pour empiler différents niveaux d'agrégation, j'utilise les requêtes dans le FROM :

Code :

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

1
2
3
4
5
SELECT a, MAX(Nombre)
FROM (SELECT a, b, COUNT(*) AS Nombre
      FROM laTable
      GROUP BY a, b)
GROUP BY a

**fadex** · 02/04/2006, 23h09

Bonjour

je me suis tomber sur cette descution donc voila j'ai lu et j'ai executé vos requetes mais :

Code :

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

1
2
3
SELECT Max(Count(*))  FROM deplacement GROUP BY matr_choffeur
 
Impossible d'exécuter une fonction d'agrégation sur une expression comportant un agrégat ou une sous-requête.

je travaille sous Sql Server.

donc j'ai le meme probleme je cherche a afficher Le(s) chauffeur(s) ayant effectué le plus de déplacements et j'arrive pas a trouver une solution , j'ai essayer avec :

Code :

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

1
2
select nom , max(n) from (select c.nom , count(d.matr_ch) as n from deplacement 
as d join chauffeurs as c on c.mat=d.matr_ch group by c.nom ) as a group by nom

mais le resultat et la meme si j'execute

Code :

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

1
2
select c.nom , count(d.matr_ch) as n from deplacement 
as d join chauffeurs as c on c.mat=d.matr_ch group by c.nom

il retourne pas le max

Merci d'avance

**Yanika_bzh** · 03/04/2006, 03h00

Le resultat est logique, en effet :

ta sous requete te ramene les chauffeurs groupés par leur nombre de déplacement (fonction d'aggregation count oblige), donc quelque chose dans ce genre :

Chauffeur1 14
Chauffeur2 43
Chauffeur3 6
Chauffeur4 9

Dans ceci, tu demandes apres les lignes dont le nombre de déplacement est max, mais par chauffeur (le nom est dans le group by). Or comme le résultat de ta sous requete est deja un aggrégat filtré sur le nom, le SGBD te renvoi donc exactement la meme chose.

Dans ton cas, compte tenu de ton SGBD (Sql Server), si tu veux quelque chose de rapide en terme de solution utilise la fonction TOP 1, qui te renverra la premiere ligne des resultats, une requete du genre :

Code :

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

1
2
3
4
 
SELECT TOP 1 * c.nom , count(d.matr_ch) as n  
   FROM deplacement AS d join chauffeurs AS c on c.mat=d.matr_ch GROUP BY c.nom 
ORDER BY 2

Je dis du genre, car je ne connais pas Sql Server, j'ai juste appercu ici ou la, que cette fonction existait.

Sinon, il te faudra refaire une requete plus standard SQL. Avec des scripts de creation et un jeu d'essai, j'aurai peut etre fait l'effort de t'aider un peu plus

Bon courage[/code]

[Access] Trouver qui a le plus grand nombre de visites

Langage SQL

Discussions similaires

Partager

Partager