Hi, I am trying to work out how to get a unique count for the following table:

ID | Agent
11 | 1
11 | 1
11 | 2
22 | 3
22 | 3

I write the following query:

SELECT Table1.ID, Count(Table1.Agent) AS CountOfAgent
FROM Table1
GROUP BY Table1.ID;

which gives me the total of agents:

ID | CountofAgent
11 | 3
22 | 2

but what i would like is to have a unique count of agents, with the following results:

ID | CountofAgent
11 | 2
22 | 1

Can any1 help please?

Thanks in advance.

You can use a subquery (the indented bit) to get the unique values first:

SELECT ID,Count(ID) AS CountOfAgent
FROM
(SELECT DISTINCT ID, Agent
FROM Table1) AS D
GROUP BY ID

hth
Chris

Thanks Stopher, that works brilliantly. I was just wondering if the column name was Agent Name rather than Agent would i edit like the following:

SELECT ID, Count(ID) AS CountOfAgent
FROM [SELECT DISTINCT ID, Agent Name FROM Table1]. AS D
GROUP BY ID;

as this gives me a syntax error, where am i going wrong?

Thanks once again, btw.

Chris is probably having Christmas dinner. The inadvisable space in the field name will require brackets:

[Agent Name]

Thanks pbaldy. I have added the brackets but this gives me an "invalid bracketing error"

This is the sql:


SELECT ID, Count(ID) AS CountOfAgent
FROM [SELECT DISTINCT ID, [Agent Name] FROM Table1]. AS D
GROUP BY ID;


Thanks once again.

Note the parentheses in the subquery SQL Chris posted vs the brackets and period you have (which in your defense I've seen the QBE add by itself).

Thanks pbaldy, my error.

just for future purposes and for building my knowledge, if i wanted to do the same with a manager name besides the agent name, im guessing i would have to add another subquery right?

(SELECT DISTINCT ID, [Agent Name]
FROM Table1) AS D
(SELECT DISTINCT ID, [Manager Name]
FROM Table1) AS D

Thanks once again.