Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
This should be a straightforward question to answer, but can you provide the output of SHOW CREATE TABLE tablename\G for the table you want help with? You don't mention a date field in your table, but its data type is important to the answer. –  Nathan Jolly May 6 '13 at 9:13
    
dev.mysql.com/doc/refman/5.5/en/… if you're using a date datatype –  FreshPhilOfSO May 6 '13 at 11:04
add comment

1 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 |
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.