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

Salil · Accepted Answer · 2010-04-07T11:25:04.303

27

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

edited Apr 07 '10 at 11:25

answered Apr 07 '10 at 11:17

Salil

46,566
21
122
156

1

Hey its uncanny, how did you know his table was called mytable too? And you used financial_year with an underscore.. – CResults Apr 07 '10 at 11:40
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 – Salil Apr 07 '10 at 12:15
@Salil what if I want to display month wise of each financial year – Bhargav Venkatesh Jul 04 '19 at 12:54

CResults · Answer 2 · 2010-04-07T10:55:43.037

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)

edited Apr 07 '10 at 10:55

answered Apr 07 '10 at 10:49

CResults

5,100
1
22
28

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.) – Steve A Sep 13 '21 at 00:04

score 1 · Answer 3 · answered Jan 06 '20 at 08:43

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

SQL query to retrieve financial year data grouped by the year

3 Answers3

Linked