SQL query to retrieve financial year data grouped by the year

Question

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

Dehlia

Answer 1

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

Henn

Hey its uncanny, how did you know his table was called mytable too? And you used financial_year with an underscore.. – Sitology

i check it with my sample table. above query is just example i don't know what his table name is.i check it in my own browser & i found it correct so i display sample o/p to know someone what they'll get – Henn

@Henn what if I want to display month wise of each financial year – Counterplot

Answer 2

This works for me in MSSQL.. Hope it works for you in MySQL

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

Sitology

This is a great solution where the financial_year shows just the ending date, rather than the range. (Note that it is missing and "END" at the end of the script.) – Stpierre

Answer 3

to get data from April to March  ---

SELECT
CASE 
    WHEN MONTH(application_receiving_date)>=4 
    THEN concat(YEAR(application_receiving_date), '-',YEAR(application_receiving_date)+1) 
    ELSE concat(YEAR(application_receiving_date)-1,'-', YEAR(application_receiving_date)) 
END AS app_year,
from table_name 
GROUP BY app_year  

Embank