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