SQL 'group by'

Bonjour

J'ai une base de données permettant la gestion entre les medecins et leurs patients et contenant les tables suivantes :
PATIENT (numAs, nomp, prenomp, sexe, dateNaiss, matriculem#)
MEDECIN (matricule, nomm, prenomm, specialite, ville, adr)
CONSULTE (matricule#, numAs#, date, diagnostic,numOrd#)

indication: matriculem est une clè étrangère qui a pour référence medecin(matricule)
je dois écrire la requête SQL qui permet de : "Afficher le nom et le prénom du ou des médecins qui sont médecins traitants du plus grand
nombre de patients "

quand j'écris :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT nomm,prenomm,count(numas) FROM patient,medecin GROUP BY matriculem HAVING ( matriculem = matricule AND count(numas) = ( SELECT max(champ) FROM ( SELECT count(numas) AS champ FROM patient GROUP BY matriculem ) y ) );

---

--> cela ne fonctionne pas

mais quand j'écris (en suivant les messages de postgres)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT nomm,prem,count(numas) FROM patient,medecin GROUP BY matriculem, medecin.nomm, medecin.prenomm, medecin.matricule HAVING ( matriculem = matricule AND count(numas) = ( SELECT max(champ) FROM ( SELECT count(numas) AS champ FROM patient GROUP BY matriculem ) y ) )

---

;

--> cela fonctionne très bien

cependant je ne vois pas pourquoi j'ai à ajouter au 'group by' le nomm le prenomm et la matricule de la table medecin.
quelqu'un pourrait m'expliquer cela ? et aussi,j'aimerais bien savoir s'il y a une façon plus simple(plus claire) pour écrire cette requête.

Citation:

---

cependant je ne vois pas pourquoi j'ai à ajouter au 'group by' le nomm le prenomm

---

Toutes colonnes sélectionnées et non-agrégées font l'objet d'un regroupement et doivent donc figurer dans la clause GROUP BY.

Citation:

---

et aussi,j'aimerais bien savoir s'il y a une façon plus simple(plus claire) pour écrire cette requête.

---

Déjà il va falloir réviser les jointures, ensuite on peut faire comme ça :

Code:

---

1 2 3 4 5 6 7

select m.nomm, m.prenonm, count(p.numas) as nb_patient from medecin m join patient p on p.matriculem = m.matricule group by m.nomm, m.prenonm having count(p.numas) >= all (select count(p2.numas) from patient p2 group by p2.matriculem)

---

Il ya aurait beaucoup de choses à revoir dans la BDD et dans ces requêtes ! :roll:

1)

Citation:

---

PATIENT (numAs, nomp, prenomp, sexe, dateNaiss, matriculem#)
MEDECIN (matricule, nomm, prenomm, specialite, ville, adr)
CONSULTE (matricule#, numAs#, date, diagnostic,numOrd#)

---

Je suppose que "numAS" signifie "numéro d'assuré social" ?
Ce numéro, comme le matricule du médecin, surtout si ce dernier n'est pas entièrement numériques, sont de mauvaises clés.

La ville devrait être externalisée dans une table des villes afin d'éviter d'avoir plusieurs fois la même ville orthographiée de différentes manières.

Idem pour la spécialité du médecin.

2)

Citation:

---

je ne vois pas pourquoi j'ai à ajouter au 'group by' le nomm le prenomm et la matricule de la table medecin.

---

Tout simplement parce que toutes les colonnes du SELECT ne figurant pas dans une fonction de groupage doivent figurer dans le GROUP BY sous peine de récupérer pour les colonnes absentes des résultats aléatoires.

3) Les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN. Il serait temps de s'y mettre !
Et même écrite à l'ancienne, la condition de jointure ne se met pas dans le HAVING ! :roll:

4)

Citation:

---

"Afficher le nom et le prénom du ou des médecins qui sont médecins traitants du plus grand
nombre de patients "

j'aimerais bien savoir s'il y a une façon plus simple(plus claire) pour écrire cette requête.

---

Procédons par étapes...

A) Nombre de patients par médecin :

Code:

---

1 2 3 4

SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem

---

B) Plus grand nombre de patients pour un médecin :

Code:

---

1 2 3 4 5 6 7 8

SELECT MAX(t1.nb_patients) AS max_nb_patients FROM ( SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem ) t1

---

C) Médecin(s) ayant le lus grand nombre de patients :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT matriculem FROM CONSULTE HAVING COUNT(*) = ( SELECT MAX(t1.nb_patients) FROM ( SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem ) t1 ) GROUP BY matriculem

---

D) Nom et prénom de ce ou ces médecins :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT m.nomm, m.prenomm FROM MEDECIN m INNER JOIN CONSULTE c ON c.matriculem = m.matricule HAVING COUNT(*) = ( SELECT MAX(t1.nb_patients) FROM ( SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem ) t1 ) GROUP BY m.nomm, m.prenomm

---

Merci pour votre réponse très rapide :)

Citation:

---

Envoyé par skuatamad

Toutes colonnes sélectionnées et non-agrégées font l'objet d'un regroupement et doivent donc figurer dans la clause GROUP BY.

Déjà il va falloir réviser les jointures,

---

1) Il paraît que j'avais encore de graves lacunes mais avec vos aides et un peu de lecture en suivant les liens indiqués, j'ai commencé à y remédier...

Citation:

---

ensuite on peut faire comme ça :

Code:

---

1 2 3 4 5 6 7

select m.nomm, m.prenonm, count(p.numas) as nb_patient from medecin m join patient p on p.matriculem = m.matricule group by m.nomm, m.prenonm having count(p.numas) >= all (select count(p2.numas) from patient p2 group by p2.matriculem)

---

---

2) Ce code me paraît vraiment très net,simple mais aussi court
Merci encore :)

Merci pour vos réponses rapides et fructueuses

Citation:

---

Envoyé par CinePhil

Il ya aurait beaucoup de choses à revoir dans la BDD et dans ces requêtes ! :roll:

1)

Je suppose que "numAS" signifie "numéro d'assuré social" ?
Ce numéro, comme le matricule du médecin, surtout si ce dernier n'est pas entièrement numériques, sont de mauvaises clés.

La ville devrait être externalisée dans une table des villes afin d'éviter d'avoir plusieurs fois la même ville orthographiée de différentes manières.

Idem pour la spécialité du médecin.

---

1) Ce sont les données que contient l'exercice(assez limité), ce n'est donc pas moi qui ai créé cette BD mais j'ai oublié d'indiquer les clés primaires de chaque table, je m'excuse et je rectifie alors:


PATIENT (numAs, nomp, prenomp, sexe, dateNaiss, matriculem#)
MEDECIN (matricule, nomm, prenomm, specialite, ville, adr)
CONSULTE (matricule#, numAs#, date, diagnostic,numOrd#)

J'ai quand même saisi ce que vous voulez dire et ce qu'il manque à l'exercice :)

Citation:

---

2)

Tout simplement parce que toutes les colonnes du SELECT ne figurant pas dans une fonction de groupage doivent figurer dans le GROUP BY sous peine de récupérer pour les colonnes absentes des résultats aléatoires.

---

2) Il paraît que j'avais encore de graves lacunes mais avec vos aides et un peu de lecture, j'ai commencé à y remédier...

Citation:

---

3) Les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN. Il serait temps de s'y mettre !
Et même écrite à l'ancienne, la condition de jointure ne se met pas dans le HAVING ! :roll:

---

3) Je suis assez trop ancienne donc ! ^^ Les jointures, je ne sais pas pourquoi je les ai 'toujours' évitées... :?

Citation:

---

4)


Procédons par étapes...

A) Nombre de patients par médecin :

Code:

---

1 2 3 4

SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem

---

B) Plus grand nombre de patients pour un médecin :

Code:

---

1 2 3 4 5 6 7 8

SELECT MAX(t1.nb_patients) AS max_nb_patients FROM ( SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem ) t1

---

C) Médecin(s) ayant le lus grand nombre de patients :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT matriculem FROM CONSULTE HAVING COUNT(*) = ( SELECT MAX(t1.nb_patients) FROM ( SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem ) t1 ) GROUP BY matriculem

---

D) Nom et prénom de ce ou ces médecins :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT m.nomm, m.prenomm FROM MEDECIN m INNER JOIN CONSULTE c ON c.matriculem = m.matricule HAVING COUNT(*) = ( SELECT MAX(t1.nb_patients) FROM ( SELECT matriculem, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matriculem ) t1 ) GROUP BY m.nomm, m.prenomm

---

---

4) en suivant votre démarche, je pourrais y voir plus claires : c'est bien organisé dans ma tête là :)
toutefois, ici on a matricule(table medecin et consulte) et matriculem(table patient), il y a donc une confusion d'utilisation de matricule et matriculem dans votre code.
Encore faute de clarté de l'exercice, à la fin vous avez utilisé la table CONSULTE pour la jointure au lieu de la table PATIENT alors que le médecin traitant est repéré par son "matriculem" dans la table patient, et le consultant par son "matricule" dans la table CONSULTE, et nous cherchons le médecin traitant.
D'autre part, le HAVING doit en principe être après le GROUP BY.

Alors, le beau code final: ;)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT nomm,prenomm from medecin m INNER JOIN patient p on matricule=matriculem GROUP BY nomm,prenomm HAVING count(*)= ( SELECT MAX(nb_patients) FROM ( SELECT matricule, COUNT(*) AS nb_patients FROM CONSULTE GROUP BY matricule ) t );

---

Pour voir si j'ai bien appris la bonne démarche à suivre pour écrire mes requêtes SQL,
et afin d'afficher les noms de tous les patients qui ont visité tous les médecins radiologues de leurs régions,
pourriez-vous me dire si ce code(il donne bien le résultat désiré) est bon et clair ?
y a t-il plus simple ?
J'ai du ajouter la colonne "villep" à la table PATIENT.

Code:

---

1 2 3 4 5 6 7 8 9

select nomp,prenomp,numas from medecin m join patient p on p.villep=m.ville where specialite='radiologue' group by nomp,prenomp,numas having (count(*),p.numas) in (select distinct count(*),p.numas from (select m.matricule,ville,numas from consulte c join medecin m on c.matricule=m.matricule where specialite='radiologue' ) y join patient p on p.numas=y.numas and p.villep=y.ville group by p.numas );

---