CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
carlofacose
carlofacose

Reputation: 35

Multiple SQL query in NodeJS

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?

Upvotes: 0

Views: 102

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

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);

Upvotes: 1

Related Questions