PDA

View Full Version : How can I write this query?



neuron
Sep 15th, 2005, 05:11 PM
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
Sep 15th, 2005, 05:19 PM
SELECT DISTINCT name, date, COUNT(name)
FROM [table]
GROUP BY name, date

Kid Charming
Sep 15th, 2005, 05:54 PM
You don't need the DISTINCT -- your GROUP BY does the same thing.

neuron
Sep 16th, 2005, 08:45 AM
Thanks a lot, guys. Let me try it. :)

neuron
Sep 17th, 2005, 06:02 AM
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!