Select per month

Question

I've got the following table:

purchases(id, item, user, price, time);

The time field is a timestamp.

I need a query that would return one row per month and that row would contain the sum of price for each item in that month.

Answer 1

Try this:

SELECT MONTH(`time`) AS month, SUM(price) AS price
FROM your_table
GROUP BY MONTH(`time`)

If you have more than one year's data you may also want to include the year in your group by:

SELECT YEAR(`time`) AS year, MONTH(`time`) AS month, SUM(price) AS price
FROM your_table
GROUP BY YEAR(`time`), MONTH(`time`)

Answer 2

what about GROUP BY YEAR(DATE(time)) ASC, MONTH(DATE(time)) ASC?