How do I retrieve multiple data from MySQL and sum them based on same fields different inputs with u-CodePudding

I am currently using nodejs to build a form that retrieves revenue based on input of the weight and parcel type.

Users in the form are able to select the weight and type (both are dropdown with options select) and will be directed to the next page that will display the revenue. However, the users are able to add rows (up to 5 times) so there can be 5 different weight and 5 different parcel types. Using HTML and Javascript, I have created the table to add new row upon clicking "Add new".

I am currently able to retrieve revenue based on a single row entry. I am unable to retrieve the revenue based on more rows (up to 5). The revenue must be sum up together if there are more than 1 row.

Here are my codes:

Upon retrieving information from MySQL, these are my current code:


var getsql2 =`SELECT Revenue FROM revenuecard where weight = '${weight}' and type = '${parceltype}';
con1.query(getsql2, function (err, data, fields) {
if (err) throw err;
console.log(data);
var revenue = JSON.stringify(revenue);
//trim the revenue and remove the brackets
revenue = parseFloat(revenue);

//data returns the output of the revenue

In HTML to display the price


<label style="text-align:right; padding-right:25px;" id="revenue" > <%= revenue %>  </label>

I am able to retrieve and display the revenue based on the 1 weight and type. However, when I add new row and have another row of weight and type, I am unable to retrieve the data. I want to add both revenue up. E.g. if There are 3 rows, the revenue calculated from 1st row will add with 2nd and 3rd row based on the different weight and type selected for each row. Is there a way to approach this?

CodePudding user response：

Your SQL query should look something like this:

SELECT 
   SUM(Revenue)
FROM
   revenuecard
WHERE
       weight IN (w1,w2,w3 ...)
   AND type IN (t1, t2, t3, ....)

w_n and t_n comes from your table

CodePudding user response：

// You should try this query
SELECT 
   *, (SELECT SUM(Revenue) FROM revenuecard WHERE id = rc.id) as total 
FROM
   revenuecard as rc
WHERE weight IN (w1,w2,w3)
   AND type IN (t1, t2, t3)