Count duplicate rows once

[sarahnice]

Hi all,

I have a table that I need to count the records, grouping them by proj_id and date_entry. This is fine until I try to only count duplicate record once.

The table is as follows

Management:
proj_id, date_entry, name

What I want to do is to have a count of all projects grouped by year. If a project has multiple entries on the same day, then I only want to count that project once.

Example Data:
1 01/03/2005 test1
1 01/04/2004 test2
2 01/03/2005 test3
1 01/03/2005 test4
1 02/04/2004 test5

The query should give the following results:
proj_id date_entry count
1 2004 2
1 2005 1
2 2005 1

I hope someone can help.
Thanks in advance
Sarah

 

[yelworcm]

If the names of the tests are not too important, how about:

select proj_id, date_entry, count(*) as count
from (select distinct proj_id, date_entry from table) a