...

View Full Version : How can I write this query?



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

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

neuron
09-16-2005, 07:45 AM
Thanks a lot, guys. Let me try it. :)

neuron
09-17-2005, 05: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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum