Count function

[SteveWright]

Hi guys,

Ive tried to find a solution to this on here with no avail, hopefully one if you can help.

I need to have a field in my query that counts the occurences of ID. i.e.

ID : Count
1 : 2
1 : 2
2 : 1

Any help with the snytax will be much appreciated.

Thanks.

 

[james_lankford]

select 
  id, 
  count(*) as number_of_times_id_occurs
from 
  your_table
group by 
  id

open the query builder, select view sql, paste the above in with your name changes

of course that will only list each id once
after looking at your post again, I don't think that's what you want

 

[Phildaburn]

If you link the results of the SQL query above on your id field, you can obtain the results you're after.