How do I group by month and year when only having a datetime field?

Question

I have a table schema which is essentially a bunch of transaction info with a datetime field

TRANSACTION (< transactionid >, amount, when)

I need to generate a monthly total of transactions, which is SUM(amount), but I stumped by what do I group by. Each row from SQL should contain the monthly total (so one row for Jan 09, Feb 09....Jan 2010 and so on). I am thinking that I may have to generate the table by code, but would like to learn if there is a way to resolve this using SQL.

Any help would be appreciated! (Using MySQL 5.3, PHP5)

Answer 1

You need to group by extracts.

SELECT 
    SUM(amount)
FROM 
    transaction
GROUP BY 
    EXTRACT(MONTH FROM when),
    EXTRACT(YEAR FROM when)

And if you need those columns, then

SELECT
    EXTRACT(MONTH FROM when) as month, 
    EXTRACT(YEAR FROM when) as year, 
    SUM(amount)
FROM 
    transaction
GROUP BY 
    month,
    year

Of course you can append ORDER BY and use short names too:

SELECT 
    EXTRACT(MONTH FROM when) as month, 
    EXTRACT(YEAR FROM when) as year, 
    SUM(amount)
FROM 
    transaction
GROUP BY 
    month, 
    year
ORDER BY 
    year DESC, 
    month DESC

Answer 2

    SELECT EXTRACT(YEAR_MONTH FROM when), sum(amount)
      FROM TRANSACTION
  GROUP BY EXTRACT(YEAR_MONTH FROM when)