CODEWITHSUNDEEP
mysqlfinancial
mlevit
mlevit

Reputation: 2736

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

Upvotes: 9

Views: 23667

Answers (3)

Vikram Singh Katewa
Vikram Singh Katewa

Reputation: 419

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

Upvotes: 2

Salil
Salil

Reputation: 47532

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

Upvotes: 29

CResults
CResults

Reputation: 5105

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)

Upvotes: 2

Related Questions