[SOLVED] MySql GROUP BY date_time and SUM quantity

Issue

In report table, there are multiple date_time and quantity records for each day. I want to select sum of quantity for each day’s record separately.

connection.query("SELECT  date_time, quantity FROM report WHERE date_time BETWEEN ? AND  ?", [fromDate_query, toDate_query], function (err, rows) {
  if (err) console.log(err);
  else {
    for (var i = 0; i < rows.length; i++) {
      var row = rows[i];
      console.log("**********************************");
      console.log("date_time : " + row.date_time);
      console.log("quantity : " + row.quantity);
      console.log("**********************************");
    }
    io.emit("chart", rows);
  }
});

Table Example

| date_time           | quantity
-------------------------
| 2017-08-13 11:12:51 | 1
| 2017-08-13 12:45:51 | 1
| 2017-08-13 13:57:51 | 1
| 2017-08-14 14:23:51 | 1
| 2017-08-14 16:34:51 | 1
| 2017-08-15 16:21:51 | 1
| 2017-08-16 14:31:51 | 1

After selection

| date_time  | quantity
 -------------
| 2017-08-13 | 3
| 2017-08-14 | 2
| 2017-08-15 | 1
| 2017-08-16 | 1
    

How can I make this happen?

Solution

Convert the timestamp to a date and GROUP BY the date and finally SUM up the quantity or do a COUNT(quantity)

  SELECT DATE(date_time)
        ,SUM(quantity) 
    FROM report
   WHERE date_time BETWEEN ? AND  ?
GROUP BY DATE(date_time)

Answered By – Esteban P.

Answer Checked By – Clifford M. (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.