[Solved] Find number difference between two sum queries

Discuss the database features
Post Reply
jnmac
Posts: 2
Joined: Fri Nov 27, 2020 6:26 am

[Solved] Find number difference between two sum queries

Post by jnmac »

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
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Find number difference between two sum queries

Post by chrisb »

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

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
jnmac
Posts: 2
Joined: Fri Nov 27, 2020 6:26 am

Re: Find number difference between two sum queries

Post by jnmac »

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
Post Reply