Resolve Error SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

I have a POS and am using Nodejs with Mysql I have a query to get order information using group clause but cannot solve it. Any guidance will help me solve this. please check the code below.

   let sql = 'SELECT TransactionID,SUM(Amount) as Amount,TransactionDate,TransactionTime FROM ordersdb GROUP BY TransactionID';

let query = mysqlConnection.query(sql, (err, rows, fields)=>{
  if(!err){
    let sql1 = 'SELECT * FROM ordersdb'
    let query1 = mysqlConnection.query(sql1, (err1, rows1, fields1)=>{
      if(!err1){
        res.render('orders.ejs',{
          orders:rows, sub_orders:rows1, selected_item:'None', month_name:'None', year:'None'
        });
       }
       else
        console.log(err1)
    })

Does this answer your question? [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) — astentx, Feb 27 '23 at 05:34
You should tag only applicable DB. MySQL and oracle are 2 different DBs. — Ankit Bajpai, Feb 27 '23 at 05:58

score 1 · Accepted Answer · answered Feb 27 '23 at 06:12

This is query that failed:

  SELECT TransactionID,              --> non-aggregated
         SUM (Amount) AS Amount,
         TransactionDate,            --> non-aggregated
         TransactionTime             --> non-aggregated
    FROM ordersdb
GROUP BY TransactionID               --> only one non-aggregated column

Error says that all non-aggregated columns (or expressions) have to be part of the group by clause. In your case, it means that valid query would be

  SELECT TransactionID,
         SUM (Amount) AS Amount,
         TransactionDate,
         TransactionTime
    FROM ordersdb
GROUP BY TransactionID, 
         TransactionDate, 
         TransactionTime;

However, is is questionable whether it'll return desired result because it kind of presumes that there can be more than just one row per transaction in not just one day, but at the same time; otherwise, your query doesn't make much sense.

How is query supposed to look like? No idea; it depends on what you actually want. Total amount per transaction?

  SELECT TransactionID,
         SUM (Amount) AS Amount
    FROM ordersdb
GROUP BY TransactionID;

Total amount per transaction per day?

  SELECT TransactionID,
         SUM (Amount) AS Amount,
         TransactionDate
    FROM ordersdb
GROUP BY TransactionID, 
         TransactionDate;

Something else? What?

Resolve Error SELECT list is not in GROUP BY clause and contains nonaggregated column

1 Answers1