Msql last year data only

Question

With mysql i am trying to fetch amount which is donated last year only,

for exampe: this is my sample table. here i would like to fech amount which is donated in last year only. here most recent donation is done on oct-14, so i want data from oct -14 to oct - 13 and not sep -13

+--------+-------+------+---------+
| nid | amount | time | month |
+--------+-------+------+---------+
| X   |   30   |      |sep-13 |
+--------+-------+------+---------+
| X   |   30   |      |sep-13 |
+--------+-------+------+---------+
| A   |   10   |      |oct-13 |
+--------+-------+------+---------+
| A   |   10   |      |oct-13 |
+--------+-------+------+---------+
| B   |   20   |      |oct-14 |
+--------+-------+------+---------+
| C   |   20   |      |oct-14 |
+--------+-------+------+---------+

can you check or searching first before asking a question? http://stackoverflow.com/questions/5027687/selecting-all-records-from-one-year-ago-till-now — Josua Marcel C, Oct 14 '14 at 01:03

score 0 · Answer 1 · answered Oct 14 '14 at 01:03

0

If your 'month' column is a date column, you can use EXTRACT function.

SELECT * FROM _table_ WHERE EXTRACT(year from `month`) = 13;

answered Oct 14 '14 at 01:03

Marco Aurélio Deleu

4,279
4
35
63

score 0 · Answer 2 · answered Oct 14 '14 at 01:20

SELECT * FROM TABLE WHERE RIGHT(MONTH, 2) = '13'

IF THE DATE LAST YEAR IS A STATIC VALUE AND WON'T NEED TO BE ADJUSTED FOR A YEAR TO YEAR PROJECT, WHETHER IT'S VARCHAR OR DATE THIS VALUE WILL BE TREATED AS A STRING AND THE RIGHT 2 CHARACTERS WILL REMAIN THE SAME. -Sorry about the caps.

If you're looking for a dynamic approach...

SELECT * FROM TABLE WHERE STR_TO_DATE(MONTH, '%Y') = YEAR(CURDATE() - 1)

OR something like the above. Again, this is dependent on the data type that defines that field. But this should do the trick.

score 0 · Answer 3 · answered Oct 14 '14 at 17:35

If you need to return rows based on maximum year in table then one of these queries can help:

If month is DATE type:

SELECT *
  FROM sample_table
  WHERE YEAR(month) = YEAR((SELECT MAX(month) FROM sample_table))

If month is character type:

SELECT *
  FROM sample_table
  WHERE RIGHT(month,2) = (SELECT MAX(RIGHT(month,2)) FROM sample_table)

Msql last year data only

3 Answers3