SQL 'group by' [Résolu]

[AmHope]

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.

[skuatamad]

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.

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)

[CinePhil]

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

1)

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)

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 !

4)

"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

[AmHope]

Merci pour votre réponse très rapide

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...

ensuite on peut faire comme ça :

Code :

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

[AmHope]

Merci pour vos réponses rapides et fructueuses

Envoyé par CinePhil

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

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

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...

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 !

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

4)


Procédons par étapes...

A) Nombre de patients par médecin :

Code :

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

Code :

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

Code :

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

Code :

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
						);

[AmHope]

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 );