Multiple SQL query in NodeJS

Question

I have to perform nested queries to return values from the database for a chart. I have to do a total of 12 queries, in which each query adds up the number of customers in each month of the year. If I use this mode, I get the right result, but written in a horrible way because I do so many lines of code:

sql = "SELECT SUM(customer) as January FROM visit WHERE date >= '2020-01-01 00:00:00' AND date <= '2020-01-31 23:59:59' AND id='4' ";
db.query(sql, function(error, result, result_fields) {
  if (error) {
      res.render('loginform.ejs', { message: err_message, level: "error" } );
      winston.error(error);
  } else {
      //SAVE DATA
      sql = "SELECT SUM(customer) as february FROM visit WHERE date >= '2020-02-01 00:00:00' AND date <= '2020-02-28 23:59:59' AND id='4'";
       //FEBRUARY
       db.query(sql, function(error, result, result_fields) {
         if (error) {
           res.render(...);
           winston.error(error);
         } else {
           req.session.february_customers = ((result[0].february == null)) ? 0 : result[0].February;
...
//This is followed by another 10 queries for the other 10 months of the year.

As you can see there are many lines of code !! Is there any way to optimize? For example using a for loop?

Slava Rozhnev · Accepted Answer · 2020-11-30 16:34:47Z

1

You should to use single query that returns yearly data grouped by month:

SELECT
    DATE_FORMAT(date, '%M') AS Month, 
    SUM(customer) as Visits
FROM visit 
WHERE 
    date BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' 
    AND id=4
GROUP BY MONTH(date);

answered Nov 30, 2020 at 16:34

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

1 Comment

carlofacose Over a year ago

this query into the sql variable?

Collectives™ on Stack Overflow

Multiple SQL query in NodeJS

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related