Condition sur un COUNT

Version imprimable

Bonjour,

je vais vous expliquez mon problème assez complexe :

tous d'abord j'ai une table personnel :
http://img838.imageshack.us/img838/6422/persyl.jpg
avec le matricule de la personne, nom, prenom, statut (id d'une autre table) centre (id d'une autre table) et fonction (id d'une autre table)

ensuite j'ai une table manoeuvres :
http://img708.imageshack.us/img708/8375/manwn.jpg
avec l'id de la manoeuvre,
le n° de module,
le libelle de la manoeuvre,
le service,
etc.... (moins important)

enfin j'ai ma table pointage qui regroupe les personnes qui on réalise une manoeuvres et la date de réalisation.
http://img267.imageshack.us/img267/7332/poins.jpg
pointer_sp est l'id de la personne pointer
pointer_fma est l'id de la manoeuvre pour laquelle il est pointer
et pointer_date correspond à la date du pointage

j'ai crée une requête qui m'affiche le nom des personnes et le nombres de manoeuvres (distincte) qu'ils ont réalisé avec comme condition :
que personnel.pers_centre = 5
et personnel.pers_fonction = 3

la requête fonctionne mais je souhaite ajouter une autre condition qui ne m'afficherais que les manoeuvres de la table pointage pour la quel le service est égal à "EM Formation" ! je ne sais pas ou placer cette condition ???

Voici ma requete :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, COUNT( DISTINCT po.pointer_fma ) AS pourcent FROM personnels p JOIN grade g ON g.id_grade = p.pers_grade LEFT JOIN pointage po ON po.pointer_sp = p.id_pers CROSS JOIN ( SELECT COUNT( * ) AS nb_manoeuvre FROM manoeuvres )m WHERE p.pers_centre =5 AND p.pers_fonction =3 GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) , m.nb_manoeuvre
donc je me demandé si je ne devrais pas rajouter la condition ici :

Code:

COUNT( DISTINCT po.pointer_fma WHERE manoeuvres.fma_impose = "EM Formation" )

mais ce n'est pas possible biensur !

merci d'avance pour votre aide:mrgreen:

02/06/2012, 14h13
skuatamad

Utilise CASE

Code:

COUNT( DISTINCT case when manoeuvres.fma_impose = 'EM Formation' then po.pointer_fma end)
02/06/2012, 14h29
gastoncs

Lorsque je rajoute case j'obtient :

Code:

#1054 - Unknown column 'manoeuvres.fma_impose' in 'field list'

Je reviens a mon post car j'ai avancé mai il me reste un dernier problème.

le principe général est que je souhaite avec ma requête, afficher le nom, prénom et grade des personnes avec le nombre de manoeuvres distincte réalisé dans la table pointage (cf images ci dessus)

donc voici ma requête qui fonctionne :
Code:

1 2 3 4 5 SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent FROM personnels p JOIN grade g ON g.id_grade = p.pers_grade LEFT JOIN pointage po ON po.pointer_sp = p.id_pers GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )
ainsi elle m'affiche :

Citation:

Pierre Dupont 4
Jacques malin 2
Giséle Dufour 0
Marc pierrot 0

j'ai donc fait une jointure vers table manoeuvres pour n'afficher que les personnes pour la quelle le champ fma_impose = 'EM Formation'
Donc voici la requête :
Code:

1 2 3 4 5 6 7 SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent FROM personnels p JOIN grade g ON g.id_grade = p.pers_grade LEFT JOIN pointage po ON po.pointer_sp = p.id_pers JOIN manoeuvres ON manoeuvres.id_fma = po.pointer_fma WHERE manoeuvres.fma_impose = 'EM Formation' GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )
Mais la le problème c'est que je n'obtient plus les personne qui n'ont effectuer aucune manoeuvres (c'est a dire les personne dont leur id n'est pas présent dans la table pointage) du coup j'obtient :

Citation:

Pierre Dupont 4
Jacques malin 2

(du coup marc et giséle sont absent alors qu'il ont 0 manoeuvres de pointé) comment résoudre ce soucis ????
c'est comme si le LEFT JOIN avait disparu !!!

05/06/2012, 17h24
estofilo

La condition de JOIN suivante:
ON manoeuvres.id_fma = po.pointer_fma
impose que po.pointer_fma ne soit jamais NULL, et ça contredit le LEFT JOIN qui précède, c.a.d que ça le rend équivalent à un INNER JOIN

Pour que po.pointer_fma puisse être null, il est impératif de ne pas l'utiliser dans des jointures internes. Généralement pour arriver à ça, on remplace simplement ces jointures internes par des jointures externes (LEFT JOIN ou RIGHT JOIN)

oui j'avais essayer avec LEFT JOIN a la place du JOIN ou RIGHT JOIN mais toujours le même résultat je n'ai pas les personne égal à zéro (ce qui ne sont pas dans la table pointage)

Code:

1
2
3
4
5
6
7
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN manoeuvres ON manoeuvres.id_fma = po.pointer_fma
WHERE manoeuvres.fma_impose = 'EM Formation'
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

05/06/2012, 17h41
estofilo

le LEFT JOIN manoeuvres indique qu'il n'y a pas forcément de correspondance dans manoeuvres.
Mais c'est (encore) contredit par la clause :

Code:

WHERE manoeuvres.fma_impose = 'EM Formation'
05/06/2012, 17h42
gastoncs

??? je ne comprend pas :calim2:

Pour les personnes n'ayant pas fait de manoeuvre, manoeuvres.fma_impose va être à NULL et donc la condition manoeuvres.fma_impose = 'EM Formation' intervenant après les jointures va éliminer ces personnes.

Une solution pourrait être de mettre ce filtrage avant le JOIN, en remplaçant manoeuvres par une sous-requête:
Code:

1 2 3 4 5 6 7 SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent FROM personnels p JOIN grade g ON g.id_grade = p.pers_grade LEFT JOIN pointage po ON po.pointer_sp = p.id_pers LEFT JOIN (select id_fma FROM manoeuvres WHERE fma_impose = 'EM Formation') AS ma ON ma.id_fma = po.pointer_fma GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

05/06/2012, 21h14
gastoncs

merci:ccool:
beaucoup

en faite je viens de me rendre compte que les conditions sur manoeuvres ne fonctionne pas avec cette requête, alors j'ai fait ceci :
Code:

1 2 3 4 5 6 7 SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent FROM personnels p JOIN grade g ON g.id_grade = p.pers_grade LEFT JOIN pointage po ON po.pointer_sp = p.id_pers LEFT JOIN manoeuvres ma ON ma.id_fma = po.pointer_fma WHERE ma.fma_impose = 'EM Formation' GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )
mais le probleme maintenant c'est que je n'ai pas les personnes qui n'ont effectué aucune maneouvre, ceux égal à 0 mais j'ai bien la condition EM Formation !!!

20/06/2012, 19h27
estofilo

Je ne vois pas de différence entre cette requête et la version du message #6 qui ne donnait pas satisfaction.

Quand tu écris "ça ne fonctionne pas", c'est insuffisant pour se faire une idée du problème.

Indique les données qu'il y a dans les tables
Indique la sortie de la requête à laquelle tu t'attends
Indique la sortie de la requête que tu obtiens par opposition au résultat attendu.

Pour que quelqu'un puisse reproduire le problème et tester la requête, idéalement il faut la définition des tables sous forme de CREATE TABLE et le remplissage de données d'exemple sous forme d'INSERT

A partir d'un certain de niveau de complexité de requête, on ne peut pas aider sans tester ce qu'on propose.

oui tu a raison je comprend, c'est moi qui vais trop vite, donc je m'explique :

donc tout d'abord voici ma table personnel :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--
-- Structure de la table `personnels`
--
 
CREATE TABLE IF NOT EXISTS `personnels` (
  `id_pers` int(11) NOT NULL AUTO_INCREMENT,
  `pers_matricule` varchar(20) NOT NULL,
  `pers_grade` int(11) NOT NULL,
  `pers_nom` varchar(70) NOT NULL,
  `pers_prenom` varchar(70) NOT NULL,
  `pers_statut` int(11) NOT NULL,
  `pers_centre` int(11) NOT NULL,
  `pers_fonction` int(11) NOT NULL,
  PRIMARY KEY (`id_pers`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=239 ;

Ensuite j'ai ma table grade :

Code:

1
2
3
4
5
6
7
8
9
--
-- Structure de la table `grade`
--
 
CREATE TABLE IF NOT EXISTS `grade` (
  `id_grade` int(11) NOT NULL AUTO_INCREMENT,
  `grd_grade` varchar(20) NOT NULL,
  PRIMARY KEY (`id_grade`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

Ensuite ma table pointage :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Structure de la table `pointage`
--
 
CREATE TABLE IF NOT EXISTS `pointage` (
  `id_pointer` int(11) NOT NULL AUTO_INCREMENT,
  `pointer_sp` int(11) NOT NULL,
  `pointer_fma` int(11) NOT NULL,
  `pointer_date` date NOT NULL,
  `pointer_formateur` int(11) NOT NULL,
  `pointer_valideur` int(11) NOT NULL,
  `pointer_formateur2` int(11) NOT NULL,
  `pointer_formateur3` int(11) NOT NULL,
  PRIMARY KEY (`id_pointer`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=174 ;

et enfin ma table manoeuvre :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Structure de la table `manoeuvres`
--
 
CREATE TABLE IF NOT EXISTS `manoeuvres` (
  `id_fma` int(10) NOT NULL AUTO_INCREMENT,
  `fma_module` varchar(50) NOT NULL,
  `fma_libelle` varchar(250) NOT NULL,
  `fma_impose` varchar(100) NOT NULL,
  `fma_fonction` int(11) NOT NULL,
  `fma_fonction2` int(11) NOT NULL,
  `fma_fonction3` int(11) NOT NULL,
  `fma_fonction4` int(11) NOT NULL,
  PRIMARY KEY (`id_fma`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

et donc moi je souhaite afficher le nom, prenom et grade des personnes (avec comme condition que le champ pers_centre = 1 et que pers_fontion = 2) qu'ils soient présentent ou non dans la table pointage ainsi que le nombre de manoeuvres qu'il ont réalisé (avec comme condition, sur le champ fma_impose = 'EM Formation' et aussi que fma_fonction = 2) :

donc voici la requête (proposé par 'estofilo') :

Code:

1
2
3
4
5
6
7
8
9
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN (SELECT id_fma FROM manoeuvres WHERE fma_impose = 'EM Formation' AND fma_fonction2 = 2) AS ma
 ON ma.id_fma = po.pointer_fma
WHERE p.pers_centre =  1
AND p.pers_fonction = 2
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

le soucis est qu'avec cette requête les conditions

Code:

WHERE fma_impose = 'EM Formation' AND fma_fonction2 = 2

ne sont pas prises en compte !

alors j'ai essayé à ma sauce de faire cette requête qui fonctionne c'est a dire qu'elle prend bien en compte les 2 conditions ci dessus mais elle ne m'affiche pas les personnes qui sont absente de la table pointage (cad avec pourcent = 0)

Code:

1
2
3
4
5
6
7
8
9
10
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN manoeuvres ma ON ma.id_fma = po.pointer_fma
WHERE p.pers_centre = 1
AND p.pers_fonction = 2
AND ma.fma_impose = 'EM Formation'
AND ma.fma_fonction = 2
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

c'est pour cette raison que je m'en remet à vous, d'avance merci pour l'attention que vous portez a mon probléme ! :ccool:

21/06/2012, 09h15
punkoff

Bonjour,

Vous n'avez pas compris les explications d'Estofilo dans ce cas.

Lisez ceci : http://blog.developpez.com/cinephil/...on-de-restric/

Comme ça, ça devrait fonctionner :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT p.pers_nom, p.pers_prenom, g.grd_grade, count( DISTINCT po.pointer_fma ) AS nombre_manoeuvres FROM personnels p INNER JOIN grade g ON g.id_grade = p.pers_grade LEFT JOIN pointage po ON po.pointer_sp = p.id_pers LEFT JOIN manoeuvres ma ON ma.id_fma = po.pointer_fma AND ma.fma_impose = 'EM Formation' AND ma.fma_fonction2 = 2 WHERE p.pers_centre = 1 AND p.pers_fonction = 2 GROUP BY p.pers_nom, p.pers_prenom, g.grd_grade
Nota :
J'ai supprimé le CONCAT, ce qui permettra au SGBD d'utiliser les index, s'ils existent, sur les colonnes de groupage et rendre ainsi la requête plus performante.

Une requête correctement indentée est plus facile à lire donc à comprendre et à débugguer.

salut,

petits conseils vu que tu en es aux tous débuts de ton application de gestion de stage...

préciser la valeur entre () pour les entier ne fait que préciser le nombre de chiffres significatif pas la taille de la colonne en octets

la seule façon de le faire est d'utiliser un des types d'entier existant

si je comprends bien tu fais ça pour les pompiers...

donc dimensionnes les identifiants en conséquence:
effectif des pompiers: 250000 donc ça tient sur du:

Code:

mediumint unsigned

nombre de centres: 7277 donc:

Code:

smallint unsigned

les grades moins de 255 donc:

Code:

tinyint unsigned

idem pour status et fonction du personnel

si une valeur n'a pas à être négative autan la déclarer en unsigned sous peine de perdre potentiellement l'équivalent de la moitié des valeurs possibles (la partie négative)

nommes tes contraintes et index, ça permet d'avoir un nom parlant lors d'un explain sur une requête

mets toujours les types numériques classés par taille en premier (index) puis les dates, booléen et enfin les dérivés de char, binary ou text, ça permet d'optimiser l'utilisation des buffers de table pour chaque ligne

préfère la suppression et recréation à la création en cas de non existence, ça te garantit que si tu change ta structure ça le mettra bien à jour...

ça donnerait pour tes tables:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 drop table if exists `personnels`; CREATE TABLE `personnels` ( `id_pers` mediumint unsigned NOT NULL AUTO_INCREMENT, `pers_centre` smallint unsigned NOT NULL, `pers_grade` tinyint unsigned NOT NULL, `pers_statut` tinyint unsigned NOT NULL, `pers_fonction` tinyint unsigned NOT NULL, `pers_nom` varchar(70) NOT NULL, `pers_prenom` varchar(70) NOT NULL, `pers_matricule` varchar(20) NOT NULL, constraint pk_pers PRIMARY KEY (`id_pers`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=239 ;
et donc:
Code:

1 2 3 4 5 6 drop table if exists `grade`; CREATE TABLE `grade` ( `id_grade` tinyint unsigned NOT NULL AUTO_INCREMENT, `grd_grade` varchar(20) NOT NULL, constraint pk_grade PRIMARY KEY (`id_grade`) ) ENGINE=myisam DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
j'espère que tu sais à quoi ça sert de préciser AUTO_INCREMENT=valeur dans la déclaration de table?:mouarf:

les tables avec peu de changement comme personnel ou surtout grade, statut devraient être en myisam car tu te fous de garantir une mise à jour vu que ces dernières ne bougeront pas... pour personnel ça dépend ce que tu veut garantir comme mises à jours...

pour le jeu de caractère utf-8 est pas plus mal que latin1 et il y a peu de différences (1 octet par caractère au max) après question de gout...:zoubi:

autan en myisam les clés étrangères ne sont pas prise en compte autan en innodb... oui... donc dans pointage et manoeuvre on devrait les voir... sachant que ça crée automatiquement l'index sur ces colonnes

par exemple:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 drop table if exists `pointage`; CREATE TABLE `pointage` ( `id_pointer` int unsigned NOT NULL AUTO_INCREMENT, `pointer_fma` int unsigned NOT NULL, `pointer_sp` mediumint unsigned NOT NULL, `pointer_formateur` mediumint unsigned NOT NULL, `pointer_formateur2` mediumint unsigned NOT NULL, `pointer_formateur3` mediumint unsigned NOT NULL, `pointer_valideur` mediumint unsigned NOT NULL, `pointer_date` date NOT NULL, constraint pk_pointer PRIMARY KEY (`id_pointer`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=174 ;
ici les questions à se poser:
est ce que tu fais 1 entrée de pointage (en répétant tout) par sapeur qui suit la formation? (horreur)
est ce que tu as systématiquement 3 formateurs?
ce qui engendrerait un remaniement de ce truc qui ne fera que gonfler avec le nombre de manips...
une fois que c'est fait mettre les définition de clés étrangères sur ce qui en aura encore besoin...

autre problème sur `manoeuvres` au niveau de `fma_impose`... tu nous crée une pauvre colonne de 100 caractères, définissant des valeurs clairement répétitives et tu fais une jointure (ou des recherches) dessus sans l'indexer
la seule punition pour ça est: :sm:
ces valeurs devraient être mises dans une table `fma_impose` (myisam) et dans `manoeuvres` tu ne devrais avoir qu'un pointeur sur elle... et là tu rendrais tes tables innodb et jointures drastiquement plus compactes et rapides quand tu va avoir ton nombre de manips qui va être conséquent...

à toi de voir si ça vaut pas le coup d'y penser et de revoir ton modèle de données à ce stade...:ccool:

21/06/2012, 22h22
gastoncs

merci ericd69, pour toutes cette mines d'informations, je vais prendre en compte des remarques et modifier mes tables comme il se doit, car heureusement je n'en suis que au début. en tout cas merci ca m'est bien utile