CODEWITHSUNDEEP
sqlmysql
Emanuil Rusev
Emanuil Rusev

Reputation: 35285

Select per month

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.

Upvotes: 12

Views: 13260

Answers (2)

Benoit
Benoit

Reputation: 79243

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

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839234

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`)

Upvotes: 14

Related Questions