Mysql – selecting the month day and year from a transaction table

MySQL

i want to create two queries for my table which has fields name,surname and amount paid,the first query should select the day,month and the amount paid,the second query should select a month,year in that year and the total amount paid in that month,lets say john paid $200 on 2013-05-01,$400 on 2013-05-03,while peter paid $50 on 2013-04-08, i want the first query to output

month and day   amount
  05-01          200
  05-03          400
  04-08          50

and the second query should output

month and year   total
2013-05           600
2013-04            50

i know i can use the sum aggregate function to select the total but the tricky part is how to select the day and the month in the format above ,thanks in advance

Best Answer

You will want to use the DATE_FORMAT function in MySQL to format the date for each query.

-- query 1
select date_format(date, '%m-%d') `m-d`,
  sum(amount) Total
from mytable
group by date_format(date, '%m-%d');

See SQL Fiddle with Demo. Gives a result:

|   M-D | TOTAL |
-----------------
| 04-08 |    50 |
| 05-01 |   200 |
| 05-03 |   400 |

-- query 2
select date_format(date, '%Y-%m') `Y-m`,
  sum(amount) Total
from mytable
group by date_format(date, '%Y-%m');

See SQL Fiddle with Demo. Gives the result:

|     Y-M | TOTAL |
-------------------
| 2013-04 |    50 |
| 2013-05 |   600 |
Related Question