How can I write this query?

[neuron]

Suppose I have the following table:

Name Date
----- ------------
Adam 10 Sep 2005
Adam 10 Sep 2005
Adam 10 Sep 2005
Betty 10 Sep 2005
Betty 10 Sep 2005
Adam 11 Sep 2005
Betty 12 Sep 2005
Betty 12 Sep 2005

And I want to count the number of occurrences of each name on each day.

For example, Adam has 3 occurrences for 10 Sep 2005, Betty has 2 occurrences for the same date. Adam has 1 occurrence for 11 Sep 2005, Betty has 2 occurrences for 12 Sep 2005.

How can I write this kind of query?

Thanks a lot!

[NancyJ]

SELECT DISTINCT name, date, COUNT(name)
FROM [table]
GROUP BY name, date

[Kid Charming]

You don't need the DISTINCT -- your GROUP BY does the same thing.

[neuron]

Thanks a lot, guys. Let me try it. :)

[neuron]

Yeah, it works perfect!

Now, I'd like to count the months of the same year.

If I want to count the years, I can do like this, right?
SELECT DISTINCT name, YEAR(date), COUNT(name)
FROM [table]
GROUP BY name, YEAR(date)How can I count the months of the same year? We cant use MONTH(date) as it might include counts of the months of different years.

Thanks a lot!