SQL: How to add columns for rows given an array of variable length row names

Say I have a database of items, and each item has the following columns: name, amount1, amount2, amount3. Given an array of names (that varies in length), how can I query the database to get the sum of each column for each column for the specified names? So if I have an array of “thing1” and “thing2”, it will return the sum of amount1 from thing1 and thing2, and the same for amount2 and amount 3.

For example: example db :

names = [thing1, thing2]

query = ?

output :

I have used the following query but cannot figure out how to extend it to handle a variable length array of names, nor all the columns:

SELECT SUM(amount1) AS Amount1Sum FROM table where name = "thing1" or name ="thing2";

For some additional context, this request is made from a node.js server using express.

Here is the route where it is implemented in my server (using one of the suggestions from below):

    app.post("/counter",function(req, res) {
        let items = req.body.name
        let sqlquery = "SELECT SUM(amount1) as amount1Sum, SUM(amount2) as amount2Sum, SUM(amount3) as amount3Sum FROM table where name in (?)";

        // query database
        db.query(sqlquery, items, (err, result) => {
            if (err) {
                return console.error(err.message);
            }
            console.log(result)
        });
    });

Output for checking the form with thing1 and thing2 is [ RowDataPacket { amount1Sum: 213, amount2Sum: 5432, amount3Sum: 23} ] and I confirmed that the array of names does include both thing1 and thing2. It’s just returning thing1 columns, not adding the columns for all the names.

Answer

Use WHERE name IN (?) and pass the JS array as the corresponding parameter to match a dynamic list.

names = ['thing1', 'thing2'];
connection.query(`SELECT SUM(amount1) AS amount1sum, SUM(amount2) AS amount2sum, SUM(amount3) AS amount3sum
FROM table
WHERE name IN (?)`, [names], function(err, results) {
    ...
});

Advertisement

Answer