I am dealing with the following data
sql table =>
account |ledger_num | date_from | interest | name | loan | loan_type
____________|___________|_______________|_______________|___________|_________|____________________
Book_1 | HP2269 | 2017-03-31 | 15 | Samuel | 120 | type_a
Book_3 | HP2236 | 2020-03-31 | -20 | bob | 190 | type_a
Book_1 | HP2269 | 2019-04-01 | 250 | Samuel | 1500 | type_b
Book_3 | HP2236 | 2019-04-01 | -3 | bob | 36 | type_b
Book_2 | L01 | 2020-03-31 | 16 | josh | 95 | type_a
Book_1 | HP2269 | 2022-04-01 | 400 | Samuel | 4050 | type_c
and this is what I am trying to do
var sql_1 = `SELECT SUM(loan) ,SUM(interest) FROM allYearAllAcc WHERE (account = 'Book_1' AND date_from > '2018-03-31')`
var sql_2 = `SELECT SUM(interest) FROM allYearAllAcc WHERE (account = 'Book_2' AND date_from > '2017-04-01' AND interest < 0)`
var sql_3 = `SELECT SUM(loan) FROM allYearAllAcc WHERE (account = 'Book_3' AND date_from < '2022-04-01')`
temp_db.all(``+sql_1+`UNION`+sql_2+`UNION`+sql_3+``,(err,data)=>{
console.log(data)
})
this clearly isn't working I want this to appear as an json array which I have to use it in my code
Use conditional aggregation with a single query on your table:
SELECT
SUM(CASE WHEN account = 'Book_1' AND date_from > '2018-03-31'
THEN loan ELSE 0 END) AS loan1,
SUM(CASE WHEN account = 'Book_1' AND date_from > '2018-03-31'
THEN interest ELSE 0 END) AS interest1,
SUM(CASE WHEN account = 'Book_2' AND date_from > '2017-04-01' AND interest < 0
THEN interest ELSE 0 END) AS interest2,
SUM(CASE WHEN account = 'Book_3' AND date_from < '2022-04-01'
THEN loan ELSE 0 END) AS loan2
FROM allYearAllAcc;