SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Group by YEAR()

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Group by YEAR()

    Hi all, I need your help.

    My MYSQL table:
    Code:
    Id	DateFolder
    1	Thu, 18 Nov 2010 08:00:00 +0000
    2	Sun, 31 Oct 2010 07:00:00 +0000
    3	Fri, 29 Oct 2010 07:00:00 +0000
    4	Mon, 23 Nov 2009 08:00:00 +0000
    5	Sun, 01 Nov 2009 07:00:00 +0000
    6	Mon, 22 Dec 2008 08:00:00 +0000
    7	Sun, 02 Nov 2008 07:00:00 +0000
    8	Mon, 15 Sep 2008 07:00:00 +0000
    9	Mon, 28 Jul 2008 07:00:00 +0000
    10	Fri, 20 Jun 2008 07:00:00 +0000
    11	Sun, 11 May 2008 07:00:00 +0000
    12	Tue, 06 May 2008 07:00:00 +0000
    13	Sat, 19 Apr 2008 07:00:00 +0000
    I need this output:

    Folder for the Year 2010 = 3
    Folder for the Year 2009 = 2
    Folder for the Year 2008 = 8

    Can someone help me?
    Thanks in advance.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    As you wrote yourself, group by year is the way to go. Did you try?

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, I try but not working... ???

    Code:
    SELECT DateFolder
    FROM tbl_Folder
    group by YEAR()

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT 
        YEAR(DateFolder) AS YearOfFolder
      , COUNT(*) AS NumberOfFolders
    FROM tbl_Folder
    group by YEAR(DateFolder)

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code:
    SELECT 
        YEAR(DateFolder) AS YearOfFolder
      , COUNT(*) AS NumberOfFolders
    FROM tbl_Folder
    group by YEAR(DateFolder)
    thanks but this query response with:

    PHP Code:
    Incorrect datetime value'Thu, 18 Nov 2010 08:00:00 +0000' 

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This query working, many thanks:

    PHP Code:
    SELECT 
    YEAR
    (STR_TO_DATE(DateFolder,'%a, %d %b %Y %h:%m:%s +0000'))
    AS 
    YearOfFolder 
    COUNT(*) AS NumberOfFolders
    FROM tbl_folder 
    GROUP by 
    YEAR
    (STR_TO_DATE(DateFolder,'%a, %d %b %Y %h:%m:%s +0000'))
    ORDER BY YearOfFolder DESC

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Why don't you store your dates in a date type?

  8. #8
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Why don't you store your dates in a date type?
    Because the field DateFolder is used for other queries who need this date type.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Because the field DateFolder is used for other queries who need this date type.
    you should still store it as DATE or DATETIME anyway

    use DATE_FORMAT if you need to put it back into this custom format when you retrieve it

    or better yet, do the reformatting in the application
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes.. your are right... I reformatting this application
    thanks for your help...


Bookmarks

Posting Permissions

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