How to combine multiple queries from sqlite3 table from node.js-CodePudding

I just started with sqlite3 and was stuck on using (union) and combining multiple queries, is there a proper way of doing this

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

CodePudding user response：

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;