[Solved] Find number difference between two sum queries

Discuss the database features

[Solved] Find number difference between two sum queries

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!
Last edited by robleyd on Sat Nov 28, 2020 2:17 am, edited 1 time in total.
Reason: Tagged [Solved]
Open Office 4.1.7 on Windows 10
jnmac

Posts: 2
Joined: Fri Nov 27, 2020 6:26 am

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.
Code: Select all   Expand viewCollapse view
`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.8 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.2 (Split) on Windows 10
chrisb

Posts: 262
Joined: Mon Jun 07, 2010 4:16 pm

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
jnmac

Posts: 2
Joined: Fri Nov 27, 2020 6:26 am