CODEWITHSUNDEEP
mysql
user1857692
user1857692

Reputation: 193

Return all mySQL Records for Current Year

In mySQL, I would like to return a count of the records that have a start_date within the current year (as in the year 2012, NOT within the last one year time period) and (as a separate query) a count of the records from the previous year (2011 in the case as it is now).

My start dates are stored thus: 2012-12-02. I am using PHP.

Help on how I would form these queries would be hugely appreciated.

Upvotes: 19

Views: 44234

Answers (4)

You Old Fool
You Old Fool

Reputation: 22969

As Rostyslav Pylypenko points out in a comment above, the accepted answer will not use indices. A better way to get records in the current year is:

SELECT COUNT(*)
FROM `table`
WHERE start_date >= DATE_FORMAT(NOW() ,'%Y-01-01');

You can apply the same logic to get count for previous year:

SELECT COUNT(*)
FROM `table`
WHERE start_date >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR ,'%Y-01-01')
    AND start_date < DATE_FORMAT(NOW() ,'%Y-01-01');

Upvotes: 0

Yogendra Singh
Yogendra Singh

Reputation: 34387

You may want to use YEAR function to get the YEAR from the date and use the same in comparison.

 SELECT COUNT(*)
 FROM TABLE1
 WHERE YEAR(START_DATE) = 2012;

Separate query from previous year:

 SELECT COUNT(*)
 FROM TABLE1
 WHERE YEAR(START_DATE) = 2011;

To get the year wise counts:

 SELECT COUNT(b.YEAR), b.YEAR
 FROM TABLE1 AS a JOIN 
    (SELECT DISTINCT YEAR(START_DATE) AS YEAR from TABLE1) AS b
    ON YEAR(START_DATE) = b.YEAR
 GROUP BY b.YEAR;

Upvotes: 8

Barranka
Barranka

Reputation: 21067

Two suggestions:

You can filter your data using dates:

select count(*) from tbl where start_date >= '2012-01-01'

or

select count(*) from tbl where start_date between '2012-01-01' and '2012-12-31'

Also, you can filter your data using the year() function:

select count(*) from tbl where year(start_date) = 2012

Hope this helps you

If you are using PHP (or any other high level programming language), you can build your query string on runtime to fit your needs (e.g. To filter records from different years).

To show the count of records with start_date before 2012, just change the where conditions:

select... where year(start_date) < 2012

or

select... where start_date < '2012-01-01'

Upvotes: 2

pixeline
pixeline

Reputation: 17984

SELECT COUNT(*)
 FROM TABLE1
 WHERE YEAR(START_DATE) = YEAR(CURDATE());

Upvotes: 42

Related Questions