Count values in a query

[Kostas Arvanitidis]

Hello,
I have a table:
Table1
[cityID] [clientID] [company] [percentage]
1 1 xxy 23
1 2 xxv 12
2 1 sss 11
2 3 aaa 3

I need to make a query that exports a result like this:
[clientId] [numberoftoppercent]
1 2
2 0
3 0

To explain, i want to count that client '1' topped in 2 cities, client '2'
in '0' etc.
Any help please?

 

[Ken Sheridan]

Try this:

SELECT clientID,
(SELECT COUNT(*)
FROM Table 1 AS T2
WHERE T2.clientID = T1.clientID
AND T2.percentage =
(SELECT MAX(percentage)
FROM Table1 AS T3
WHERE T3.cityID = T2.cityID))
AS numberoftoppercent
FROM Table1 AS T1;

Ken Sheridan
Stafford, England

 

[Smartin]

Ken - Very nice.

Corrected "Table 1" and added DISTINCT:

SELECT DISTINCT clientID,
(SELECT COUNT(*)
FROM Table1 AS T2
WHERE T2.clientID = T1.clientID
AND T2.percentage =
(SELECT MAX(percentage)
FROM Table1 AS T3
WHERE T3.cityID = T2.cityID))
AS numberoftoppercent
FROM Table1 AS T1;

Try this:

SELECT clientID,
(SELECT COUNT(*)
FROM Table 1 AS T2
WHERE T2.clientID = T1.clientID
AND T2.percentage =
(SELECT MAX(percentage)
FROM Table1 AS T3
WHERE T3.cityID = T2.cityID))
AS numberoftoppercent
FROM Table1 AS T1;

Ken Sheridan
Stafford, England

Hello,
I have a table:
Table1
[cityID] [clientID] [company] [percentage]
1 1 xxy 23
1 2 xxv 12
2 1 sss 11
2 3 aaa 3

I need to make a query that exports a result like this:
[clientId] [numberoftoppercent]
1 2
2 0
3 0

To explain, i want to count that client '1' topped in 2 cities, client '2'
in '0' etc.
Any help please?

 

[Kostas Arvanitidis]

Thank you both...
It worked like a charm....

Thanks again....

Ken - Very nice.

Corrected "Table 1" and added DISTINCT:

SELECT DISTINCT clientID,
(SELECT COUNT(*)
FROM Table1 AS T2
WHERE T2.clientID = T1.clientID
AND T2.percentage =
(SELECT MAX(percentage)
FROM Table1 AS T3
WHERE T3.cityID = T2.cityID))
AS numberoftoppercent
FROM Table1 AS T1;

Try this:

SELECT clientID,
(SELECT COUNT(*)
FROM Table 1 AS T2
WHERE T2.clientID = T1.clientID
AND T2.percentage =
(SELECT MAX(percentage)
FROM Table1 AS T3
WHERE T3.cityID = T2.cityID))
AS numberoftoppercent
FROM Table1 AS T1;

Ken Sheridan
Stafford, England

Hello,
I have a table:
Table1
[cityID] [clientID] [company] [percentage]
1 1 xxy 23
1 2 xxv 12
2 1 sss 11
2 3 aaa 3

I need to make a query that exports a result like this:
[clientId] [numberoftoppercent]
1 2
2 0
3 0

To explain, i want to count that client '1' topped in 2 cities, client '2'
in '0' etc.
Any help please?