mysqlfinancial

SQL query to retrieve financial year data grouped by the year


I have a database with lets assume two columns (service_date & invoice_amount). I would like to create an SQL query that would retrieve and group the data for each financial year (July to June).

I have two years of data so that is two financial years (i.e. 2 results).

I know I can do this manually by creating an SQL query to group by month then run the data through PHP to create the financial year data but I'd rather have an SQL query.

All ideas welcome.

Thanks


Solution

  • SELECT
       CASE WHEN MONTH(service_date)>=7 THEN
              concat(YEAR(service_date), '-',YEAR(service_date)+1)
       ELSE concat(YEAR(service_date)-1,'-', YEAR(service_date)) END AS financial_year,
       SUM(invoice_amount)
    FROM mytable
    GROUP BY financial_year
    

    which produce o/p like below

    financial_year   invoice_amount
    2007-2008        10000.00
    2008-2009        15000.00
    2009-2010        20000.00