Group by YEAR()

[cms9651]

Hi all, I need your help.

My MYSQL table:

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.

[guido2004]

As you wrote yourself, group by year is the way to go. Did you try?

[cms9651]

Yes, I try but not working... ???

SELECT DateFolder
FROM tbl_Folder
group by YEAR()

[guido2004]

SELECT 
    YEAR(DateFolder) AS YearOfFolder
  , COUNT(*) AS NumberOfFolders
FROM tbl_Folder
group by YEAR(DateFolder)

[cms9651]

SELECT 
    YEAR(DateFolder) AS YearOfFolder
  , COUNT(*) AS NumberOfFolders
FROM tbl_Folder
group by YEAR(DateFolder)

thanks but this query response with:

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

[cms9651]

This query working, many thanks:

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; 

[guido2004]

Why don't you store your dates in a date type?

[cms9651]

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.

[r937]

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

[cms9651]

Yes.. your are right... I reformatting this application
thanks for your help...