Menu
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
942
Write the following SQL Snippet in Query Editor. We can see the current financial year using the preceding query.
How to Group by Year in SQL 1 Database: 2 Operators: 3 Problem: You want to group your data by year. 4 Example I: One of the columns in your data is transaction_date. ... 5 Solution 1 (displaying the year and the money earned): 6 Solution 2 (displaying the complete date, the year, and the money earned in the corresponding year): More items...
The first selected column is the year extracted from the date. The second column is the aggregate function SUM (money). At the end of the query you need a GROUP BY EXTRACT (year FROM transaction_date) or, simpler, GROUP BY 1 (since EXTRACT (year FROM transaction_date) is the first column.)
You’ll certainly need to group data into years and quarters if you’re creating financial reports. I’m going to show you how to do that. Learning this principle will allow you to group data on any other level (i.e. months, weeks, or days, depending on the data you have).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
