Trouble avec la fonction COUNT

[DSDINT]

Bonjour,

J'essai de sortir le nombre de fiches ayant un C2.userdef05 différents par C1.owner et C1.state, mais ce qui m'est retourné c'est le grand total des fiches ayant un C2.userdef05. Je ne sais pas trop si je m'explique bien, mais bon voici ce que je demande et ce que j'obtien et ce que je veux obtenir

Ce que je demande:

Select distinct C1.owner, C1.state,
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Airport'),0) as 'Airport',
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Amateur'),0) as 'Amateur',
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Auto parts'),0) as 'Auto parts'
FROM contact1 C1 inner join contact2 C2 on C1.accountno=C2.accountno
WHERE C1.key5 not like 'I%' and C1.owner in ('12usa - ', '13usa - ', '7can - o', '10can - ', '8can - m', '1sales', '9can - o')

Ce que j'obtien :

10can - ON 52 1 27
10can - PA 52 1 27
10can - QC 52 1 27
12usa - AK 52 1 27
12usa - AR 52 1 27
12usa - AZ 52 1 27
12usa - CA 52 1 27
12usa - CO 52 1 27
12usa - HI 52 1 27
12usa - IA 52 1 27
12usa - ID 52 1 27
...

Ce que je veux :

10can - ON 2 0 0
10can - PA 5 1 3
10can - QC 1 0 0
12usa - AK 0 0 1
12usa - AR 0 0 0
12usa - AZ 7 0 0
12usa - CA 0 0 0
12usa - CO 2 0 0
12usa - HI 4 0 0
12usa - IA 1 0 2
12usa - ID 0 0 1
...

Que dois-je faire pour obtenir le count par owner et state? Merci!

[kagemaru]

Bonjour,

Tout était dans votre dernière phrase : il faut utiliser la clause de regroupement GROUP BY :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Select distinct C1.owner, C1.state,
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Airport'),0) as 'Airport',
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Amateur'),0) as 'Amateur',
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Auto parts'),0) as 'Auto parts'
FROM contact1 C1 inner join contact2 C2 on C1.accountno=C2.accountno
WHERE C1.key5 not like 'I%' and C1.owner in ('12usa - ', '13usa - ', '7can - o', '10can - ', '8can - m', '1sales', '9can - o')
GROUP BY C1.owner, C1.state