Count case:dénombrement sous condition

**Johnny English** · 24/09/2009, 19h46

Bonjour à tous

Petite précision tout d’abord. Bien que mon post ait l’apparence d’un exercice de TD, ce n’est pas le cas. Il ne s’agit pas de «faites mon devoir à ma place». Simplement afin de ne pas mettre en ligne d’informations professionnelles, j’ai quelque peu transformé les éléments de mon problème de travail.
Par ailleurs, je suis assez novice, alors pardonnez moi si j’ai une mauvaise façon de raisonner.

Sous SQL Server, j’ai deux tables T1 et T2,chacune ayant plusieurs colonnes mais seules indiquées concernent le problème.
T1(Caisse,…,clé primaire reliant T1 et T2,soit C1)
T2(Outils….,C1)

Je cherche à trouver combien il y a de chaque outil par caisse.

J’ai tenté la requête suivante:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select Caisse "n° de caisse",
count(case when outil='marteau' then 1 else 0 end) as nbmarteaux,
count(case when outil='pince' then 1 else 0 end) as nbpinces,
count(case when outil='tournevis' then 1 else 0 end) as nbtournevis,
count(case when outil='rabot' then 1 else 0 end) asnbrabots,
count(case when outil='scie' then 1 else 0 end) asnbscies,
count(case when outil='perceuse' then 1 else 0 end) as nbperceuses,
from T1 a,T2 b
where a.C1=b.C1
group by Caisse
order by Caisse

La requête me retourne

Code :

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

1
2
3
4
5
N° de caisse		nbmarteaux		nbprinces…..
1			          13			13
2			          34			34
3			          26			26
……..

Le problème est d’une part que les résultats retournés sont tous identiques sur chaque ligne. Ainsi, pour la caisse n°1,j’ai 13 marteaux, 13 pinces, 13 tournevis..alors que ce n’est pas le cas bien sûr. D’autre part, 13 ou 34 dans cet exemple fictif correspond au nombre total d’outils dans chaque caisse: par exemple 2 marteaux+4 pinces+1 tournevis+…..=13 outils dans la caisse 1 alors que je voudrais justement

Code :

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

1
2
3
4
5
6
 
N° de caisse		nbmarteaux		nbprinces…..
1			2			4….
2			8			6……
3			5			4…..
……..

C’est comme si j’utilisais la requête suivante pour la caisse n°1:

Code :

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

1
2
3
4
select count (*) as nboutils,Caisse “n° de caisse”
from T2
where Caisse='1'
group by Caisse

J’ai fait divers tests afin de vérifier si ma requête a un sens. Ainsi, j’ai remplacé

Code :

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

count(case when outil='marteau' then 1 else 0 end) as nbmarteaux,

par

Code :

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

count(case when outil='marteau' then 2 else 0 end) as nbmarteaux,

mais sans conséquences sur la requête qui me retourne la même chose.

Code :

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

1
2
3
4
5
6
7
select Caisse,
count(*) as nbmarteaux,
count(*) as nbpinces,
…..
from T1 C1,T2 C1
where c.C1=d.C1
group by Caisse

A noter que si je remplace count par sum, j'obtiens là aussi des résultats curieux quasiment tous nuls et en outre ma colonne outils est de type varchar comme la colonne Caisse. Alors, je me demande ce que "peut calculer la requête". A priori sum ne peut s'appliquer à une colonne de type de varchar.

Pour résumer,je ne sais pas comment compter le nombre de champs(marteau,pince..).

Merci et en espérant avoir été assez clair.

**Waldar** · 24/09/2009, 19h56

Pour vous aider complètement il aurait fallu un petit contenu des colonnes intéressantes de vos deux tables.

J'aurai effectivement utilisé des SUM à la place des COUNT, un coalesce(..., 0) autour du SUM, et surtout dans le case j'aurai écris OUTIL à la place de CAISSE.

**mikedavem** · 24/09/2009, 20h56

Bonsoir,

Il est vrai qu'il existe certaines imprécisions dans l'énoncé de votre problème mais bon voici peut etre quelque chose qui pourra peut être vous aider. Une solution avec CASE et une avec PIVOT (même si ca reste de la cosmétique)

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
DECLARE @T1 TABLE
(
 Caisse INT NOT NULL,
 NomCaisse VARCHAR(20) NOT NULL
);
 
DECLARE @T2 TABLE
(
 Outils VARCHAR(20) NOT NULL,
 Caisse INT NOT NULL
);
 
INSERT INTO @T1 VALUES (1,'Caisse1');
INSERT INTO @T1 VALUES (2,'Caisse2');
 
INSERT INTO @T2 VALUES ('marteau',1);
INSERT INTO @T2 VALUES ('marteau',1);
INSERT INTO @T2 VALUES ('marteau',1);
INSERT INTO @T2 VALUES ('Pince',1);
INSERT INTO @T2 VALUES ('Tournevis',1);
INSERT INTO @T2 VALUES ('Tournevis',1);
INSERT INTO @T2 VALUES ('Scie',1);
INSERT INTO @T2 VALUES ('Perceuse',1);
INSERT INTO @T2 VALUES ('Perceuse',1);
INSERT INTO @T2 VALUES ('Perceuse',1);
INSERT INTO @T2 VALUES ('Perceuse',1);
 
INSERT INTO @T2 VALUES ('marteau',2);
INSERT INTO @T2 VALUES ('marteau',2);
INSERT INTO @T2 VALUES ('Pince',2);
INSERT INTO @T2 VALUES ('Pince',2);
INSERT INTO @T2 VALUES ('Tournevis',2);
INSERT INTO @T2 VALUES ('Tournevis',2);
INSERT INTO @T2 VALUES ('Scie',2);
INSERT INTO @T2 VALUES ('Scie',2);
INSERT INTO @T2 VALUES ('Perceuse',2);
INSERT INTO @T2 VALUES ('Perceuse',2);
 
-- Solution avec CASE
SELECT 
	t1.caisse,
	COALESCE(SUM(CASE WHEN t2.Outils = 'marteau' THEN 1 ELSE 0 END),0) AS marteau,
	COALESCE(SUM(CASE WHEN t2.Outils = 'Pince' THEN 1 ELSE 0 END),0) AS Pince,
	COALESCE(SUM(CASE WHEN t2.Outils = 'Tournevis' THEN 1 ELSE 0 END),0) AS Tournevis,
	COALESCE(SUM(CASE WHEN t2.Outils = 'Scie' THEN 1 ELSE 0 END),0) AS Scie,
	COALESCE(SUM(CASE WHEN t2.Outils = 'Perceuse' THEN 1 ELSE 0 END),0) AS Perceuse
FROM @t1 t1
INNER JOIN @t2 t2
ON t1.Caisse = t2.Caisse
GROUP BY t1.caisse;
 
-- Solution avec PIVOT
SELECT *
FROM
(
	SELECT
		t1.Caisse,
		t2.Outils
	FROM @t1 t1
	INNER JOIN @t2 t2
	ON t1.Caisse = t2.Caisse
) AS p
PIVOT
(
	COUNT(Outils)
	FOR Outils IN ([marteau],[Pince],[Tournevis],[Scie],[Perceuse])	
) AS r
;

++

**Johnny English** · 28/09/2009, 19h47

Envoyé par Waldar

Pour vous aider complètement il aurait fallu un petit contenu des colonnes intéressantes de vos deux tables.

J'aurai effectivement utilisé des SUM à la place des COUNT, un coalesce(..., 0) autour du SUM, et surtout dans le case j'aurai écris OUTIL à la place de CAISSE.

Merci.Je ne connais pas "coalesce(..., 0)".Je suis en train de regarder tout ça.Pour ce qui est de outils à la place de caisse,autant pour moi.J'ai édité.C'est bien ce que je voulais écrire mais je me suis emmêlé les pinceaux en donnant des informations de substitution.Le problème rencontré demeure.
Pour ce qui est du contenu des colonnes,je ne sais pas vraiment ce que tu attends mais sinon,j'ai une colonne de la sorte

Code :

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

1 représente la caisse 1,2 la caisse 2..

Ce n'est pas vraiment défini.On va dire que si j'ai 5 outils dans la caisse 1,j'aurai

Code :

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

1 représente le n° de caisse. Il y a 5 fois le champ 1 car il y a 5 outils.

De même pour la colonne Outils,j'aurai

Code :

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

1
2
3
4
5
6
7
 
Outils
marteau
marteau
scie
cutter
tournevis

Il y a 5 champs,soit 5 outils.

Il faut noter que si le champ 1 ou sil le champ marteau se répètent,il ne s'agit pas de doublons,de triplets...

Voilà.Merci encore à toi et à mikedavem.
J'étudie tout ça même si j'avoue ne pas tout comprendre sur la méthode de mikedavem.

**Johnny English** · 30/09/2009, 19h55

Bonsoir

J'ai pu m'en sortir.

Code :

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

1
2
3
4
5
6
select caisse,
coalesce(sum (case when outils='marteau' then 1 end),0)as nbdemarteaux,
coalesce(sum (case when cat_libelle='cutter' then 1 end),0)as nbdecutters
from T1 a c,T2 b
where a.C1=b.C1
group by caisse

Merci waldar.

**Waldar** · 30/09/2009, 20h15

Bravo à vous, notez que mikedavem vous avait donné tout le code nécessaire.

Essayez dès maintenant d'utiliser les mots clefs JOIN dans le FROM, vous y gagnerez en lisibilité et en fonctionnalités.

Préfixez aussi toujours toutes vos colonnes dès que vous avez plus d'une table.

Code :

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

1
2
3
4
5
  select a.caisse,
         coalesce(sum(case when b.outils = 'marteau' then 1 end), 0) as nbdemarteaux,
         coalesce(sum(case when b.outils = 'cutter'  then 1 end), 0) as nbdecutters
    from T1 a INNER JOIN T2 b ON a.C1 = b.C1
group by a.caisse

**Johnny English** · 30/09/2009, 22h03

Envoyé par Waldar

Bravo à vous

Non, bravo à vous.Je n'ai fait que copier/coller votre code.

Envoyé par Waldar

notez que mikedavem vous avait donné tout le code nécessaire.

Pour être franc,je ne suis pas encore en mesure de comprendre ce type de code mais je le remercie bien sûr de s'être penché sur mon problème.

Envoyé par Waldar

Préfixez aussi toujours toutes vos colonnes dès que vous avez plus d'une table.

Comme je l'ai dit,je suis vraiment un novice et je dois dire que je ne sais pas trop ce que ça veut dire.Ce n'est pas pour demain que je vais devenir expert confirmé.

Merci encore.

Count case:dénombrement sous condition

MS SQL Server

Discussions similaires

Partager

Partager