[Solved] Find number difference between two sum queries

Discuss the database features

[Solved] Find number difference between two sum queries

Postby jnmac » Fri Nov 27, 2020 6:41 am

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

Postby chrisb » Fri Nov 27, 2020 2:56 pm

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

Postby jnmac » Sat Nov 28, 2020 2:01 am

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


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests