Hello.
I have two queries for a simple budgeting database which run fine individually. I would like to find the number difference in the results of these two queries, using SQL if possible, or any method recommended by forum members.
The queries are:
SELECT SUM( "ENTRY_AMOUNT" ) FROM "TABLE_ENTRIES" WHERE "ENTRY_TYPE" = 'INCOME'
SELECT SUM( "ENTRY_AMOUNT" ) FROM "TABLE_ENTRIES" WHERE "ENTRY_TYPE" = 'EXPENSE'
Is there a way to subtract the bottom statement (result) from the top statement (result) using one SQL statement? (This would give the "current balance" of the budget)
Thank you!
[Solved] Find number difference between two sum queries
[Solved] Find number difference between two sum queries
Last edited by robleyd on Sat Nov 28, 2020 2:17 am, edited 1 time in total.
Reason: Tagged [Solved]
Reason: Tagged [Solved]
Open Office 4.1.7 on Windows 10
Re: Find number difference between two sum queries
hello jnmac,
i have used a sub-query to calculate the totals, this is probably the most efficient method.
from the 'Base' main window:
1) hit 'Queries'
2) hit 'Create Query in SQL View...'
3) paste the code.
4) activate toolbar icon 'Run SQL command directly'
5) hit F5 to execute.
i have used a sub-query to calculate the totals, this is probably the most efficient method.
from the 'Base' main window:
1) hit 'Queries'
2) hit 'Create Query in SQL View...'
3) paste the code.
4) activate toolbar icon 'Run SQL command directly'
5) hit F5 to execute.
Code: Select all
select "INCOME", "EXPENSE", "INCOME" - "EXPENSE" "current balance"
from
(
select
sum(case when "ENTRY_TYPE" = 'INCOME' then "ENTRY_AMOUNT" else 0 end) "INCOME",
sum(case when "ENTRY_TYPE" = 'EXPENSE' then "ENTRY_AMOUNT" else 0 end) "EXPENSE"
from "TABLE_ENTRIES"
)
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Find number difference between two sum queries
Awesome! I tried subtracting the sum queries, assigning variables to them, everything I could think of, even if I knew it was wrong. My SQL knowledge is not good enough to get me to your simple solution. Perfect! Thanks so much!
Open Office 4.1.7 on Windows 10