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

Question

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

Tim Biegeleisen · Accepted Answer · 2022-03-12 11:02:01Z

1

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;

answered Mar 12, 2022 at 11:02

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

doc pinocchio Over a year ago

what is the purpose does this serve? - " THEN loan ELSE 0 END " can you please explain

Tim Biegeleisen Over a year ago

Google for "conditional aggregation SQL" ... too long for a comment

Collectives™ on Stack Overflow

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

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related