Subtracting values from two different tables in Sqlite

Solar2D General Questions/Discussion
#1

Hi,

I am just wondering if there is a specific type of syntax required for a subtraction command in Sqlite, as I am trying to subtract values from two different tables. Currently, I have tried this but with no success:

 db:exec([[SELECT SUM(incomeValue) FROM income WHERE timeStamp >= "]]..to_date..[[" AND timeStamp <= "]]..now..[[" MINUS SELECT SUM(expenseValue) FROM expense WHERE timeStamp2 >= "]]..to_date..[[" AND timeStamp2 <= "]]..now..[["]]

I was not sure whether to use MINUS, or just the - symbol.
Thanks

#2

Haven’t tried this but found these through docs and Google:

LuaSQLite documentation linked from docs:

Don’t know if it’s implemented in LuaSQLite but available in SQLite:

#3

You would not do it this way. You would have a single table that contained both values. Normally financial software would have a transactions table. Positive lines would indicate income and negative lines would indicate expenses. Much like your bank statement is laid out.

This makes running aggregate queries so much easier.

#4

It might be a wrong way to go but you can try the below query:

SELECT SUM(total_income) - SUM(total_expense) 
  FROM (
	SELECT SUM(incomeValue) AS total_income, 0 AS total_expense 
	  FROM income 
	 UNION 
	SELECT 0, SUM(expenseValue) 
	  FROM expense)

P.S: I have tried it with MySQL

#5

I can not confirm that the following will work under Solar2D because I used sqlite3 under Windows 7.

SQLite version 3.8.8.2 2015-01-30 14:30:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE income  (id INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT, timeStamp TEXT, incomeValue   NUMERIC);
sqlite> INSERT INTO  income  VALUES (NULL, "2020.10.06", 21.00);
sqlite> INSERT INTO  income  VALUES (NULL, "2020.10.07", 21.00);
sqlite> CREATE TABLE expense (id INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT, TimeStamp2 TEXT, expenseValue  NUMERIC);
sqlite> INSERT INTO  expense  VALUES (NULL, "2020.10.06", 5.00);
sqlite> INSERT INTO  expense  VALUES (NULL, "2020.10.07", 10.00);
sqlite>
sqlite> SELECT SUM(incomeValue) FROM income;
42
sqlite> SELECT SUM(expenseValue) FROM expense;
15
sqlite>
sqlite> SELECT SUM(incomeValue)-(SELECT SUM(expenseValue) FROM expense) FROM income;
27
sqlite>
sqlite> SELECT SUM(incomeValue)-(SELECT SUM(expenseValue) FROM expense WHERE timeStamp2 == "2020.10.07") FROM income WHERE timeStamp == "2020.10.07";
11
sqlite>
#6

The query you suggested is outputting 0 (which is not correct). Not sure why this may be. However, I will try SGS’s method of having both income and expenses in the same table to make things easier.

#7

I created two tables on Sqlite and filled with dummy numbers and ran the query, and it gave me the desired result.

Moreover, I have another query:
SELECT SUM(incomeValue) - (SELECT SUM(expenseValue) FROM expense) AS result FROM income

However, you should follow the way SGS suggested.

#8

Hmm, I don’t know why if it works for you why it didn’t for me… (also tried your other query), however it is outputting 0 as a result rather than nil, meaning it understands the query but isn’t grabbing the correct values.

If it were to be subtracted from the same table (the way SGS suggested), would it just be a query like:
SELECT SUM(incomeValue) - SELECT SUM(expenseValue) FROM income
thanks.

#9

Income is positive, expenses are negative, the sum of the values is the result.
What this means that you can use the same field for positive and negative values.

SQLite version 3.8.8.2 2015-01-30 14:30:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE income  (id INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT, timeStamp TEXT, incomeValue   NUMERIC);
sqlite> INSERT INTO  income  VALUES (NULL, "2020.10.06", 21.00);
sqlite> INSERT INTO  income  VALUES (NULL, "2020.10.07", 21.00);
sqlite> INSERT INTO  income  VALUES (NULL, "2020.10.06", -5.00);
sqlite> INSERT INTO  income  VALUES (NULL, "2020.10.07", -10.00);
sqlite> SELECT SUM(incomeValue) FROM income WHERE timeStamp == "2020.10.07";
11
sqlite>
#10

Okay, so instead of having a column for income and expenses, the column can just be a value for both (then positive for income and negative for expenses). So a simple query could be
SELECT SUM(incomeValue) FROM income ?

I have since tried having both income and expenses in the same table, however with incomeValue and expenseValue in different columns. Whenever income or expenses were added, the corresponding value on that line was labelled as NULL (e.g. if 10 was added in expenseValue, incomeValue would be labelled NULL on that line). I then created this query:
local currentBalance = db:exec([[SELECT SUM(incomeValue) - SELECT SUM(expenseValue) FROM income]])

The total income was 100 and the total expense was 10, therfore the printed value should come out with 90, but instead it printed 1?

#11

I assume that the 1 that was printed is the return code 1 (ERROR) from db:exec because your SQL statement is wrong. The correct syntax can be found in my previous post:

if two tables are used:

SELECT SUM(incomeValue)-(SELECT SUM(expenseValue) FROM expense) FROM income;

or with one table:

SELECT SUM(incomeValue)-(SELECT SUM(expenseValue) FROM income) FROM income;

There are two separate select from statements, for this reason the second select statement must be enclosed in brackets.

My advice is to download the slqite3 command line shell from https://www.sqlite.org/download.html and use this to check your SQL syntax.

#12

Thanks for the link, the console is outputting 0 again. I will download the command line shell and check the syntax

#13

Okay, so I tried using the command line shell on my test table with incomeValue and expenseValue in two separate rows. I found that it came out with the correct results. These are the values i entered:

sqlite> INSERT INTO income VALUES (NULL, "2020.10.06", 21.00, 00.00);

sqlite> INSERT INTO income VALUES (NULL, "2020.10.06", 00.00, 10.00);

I then added a query:

sqlite> SELECT SUM(incomeValue)-(SELECT SUM(expenseValue) FROM income) FROM income WHERE timeStamp == "2020.10.06";

In which 11 was outputted.

However, this query still doesn’t seem to work in my program. I am using sublime text 3 as the program with corona as the simulator. This is what the whole statement is:

local currentBalance = db:exec([[SELECT SUM(incomeValue)-(SELECT SUM(expenseValue) FROM income) FROM income;]])

print(currentBalance)

I don’t know if there is another syntax error in here somewhere? The db has 21 in the incomeValue and 10 in the expenseValue so the printed number should be 11, but it is printing 0.

#14

You get 0 because the result from db:exec() is supposed to be 0 which means that the query as executed successfully.

You should run like below:

for row in db:nrows("SELECT SUM(incomeValue)-(SELECT SUM(expenseValue)  FROM income) AS total FROM income") do
   print(row.total)
end

Check this link: https://docs.coronalabs.com/api/library/sqlite3/index.html

#15

Is ‘total’ another column in the db? I assumed not, however it prints nil. I also tried with total as another column but it also comes out with nil.

#16

@MOULTC15A, I edited my previous post and changed the position of “total”. I set the column name of the result as “total”

You also have to consider adding ‘WHERE timeStamp == “2020.10.06”’ to both the subquery and the query itself.

I think the below query should work:

for row in db:nrows("SELECT SUM(incomeValue)-(SELECT SUM(expenseValue)  FROM income WHERE timeStamp == "2020.10.06") AS total FROM income WHERE timeStamp == "2020.10.06"") do
   print(row.total)
end
#17

Thanks, the edit by changing the position of total has made it work. Much appreciated for the help!

#18

You do not need the sub query!

SELECT SUM(incomeValue - expenseValue) AS total FROM income WHERE timeStamp == "2020.10.06"