Group by YEAR()

Databases
1

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.

2

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

3

Yes, I try but not working… ???


SELECT DateFolder
FROM tbl_Folder
group by YEAR()
4 

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

thanks but this query response with:

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

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;
7

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

8

Because the field DateFolder is used for other queries who need this date type.

9

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

10

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