Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2005
    Location
    GMT +7
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How can I write this query?

    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!

  • #2
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    Code:
    SELECT DISTINCT name, date, COUNT(name)
    FROM [table]
    GROUP BY name, date
    Last edited by NancyJ; 09-15-2005 at 04:23 PM.

  • #3
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You don't need the DISTINCT -- your GROUP BY does the same thing.

  • #4
    New Coder
    Join Date
    Jul 2005
    Location
    GMT +7
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot, guys. Let me try it.

  • #5
    New Coder
    Join Date
    Jul 2005
    Location
    GMT +7
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Code:
    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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •