Counting Duplicates Query

[rfear]

How do I query data like this :-

Year Week
2006 10
2006 25
2007 10
2007 10
2007 20
2008 20
2008 20

to turn it into this :-

Year Week Arrival Rate
2006 10 1
2006 25 1
2007 10 2
2007 20 1
2008 20 2

I can find loads about finding duplicates, deleting duplicates, hiding duplicates but not much on counting them ?

 

[ByteMyzer]

You might try the following (substitute highlighted text with the appropriate table/field names):

SELECT T1.[B][I]Year[/I][/B], T1.[B][I]Week[/I][/B], Count(T1.[B][I]Week[/I][/B]) AS [Arrival Rate]
FROM [B][I]MyTable[/I][/B] T1
GROUP BY T1.[B][I]Year[/I][/B], T1.[B][I]Week[/I][/B];

 

[rfear]

Awesome, worked first time thanks.