Unique Count

[aftabn10]

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.

 

[stopher]

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

 

[aftabn10]

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.

 

[pbaldy]

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

[Agent Name]

 

[aftabn10]

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.

 

[pbaldy]

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).

 

[aftabn10]

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.