MYSQL Nodejs APP : GROUP BY clause and contains nonaggregated column

Question

I have this function to get Salaries,

when I execute this api,

I get that error : "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'projects_managment.salary.salaryDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by",

async function getSalaryByMonth(currentMonth) {
  const month = moment(currentMonth).month() + 1;
  const year = moment(currentMonth).year();
  const values = [currentMonth, currentMonth, month, year];
  console.log(values);
  const query = `
  SELECT 
  ${process.env.DB_SCHEMA}.salary.salaryDate as salaryDate,
  ${process.env.DB_SCHEMA}.employee.id AS employeeId,
  ${process.env.DB_SCHEMA}.employee.firstName AS firstName,
  ${process.env.DB_SCHEMA}.employee.lastName AS lastName,
  ${process.env.DB_SCHEMA}.salary.salary,
(SELECT 
          dailywage
      FROM
          ${process.env.DB_SCHEMA}.employeeDailyWage
      WHERE
          startFromDate <= ?
              AND ${process.env.DB_SCHEMA}.employeeDailyWage.employeeId = ${process.env.DB_SCHEMA}.employee.id
      ORDER BY startFromDate DESC
      LIMIT 1) AS dailyWage,
(SELECT 
          startFromDate
      FROM
          ${process.env.DB_SCHEMA}.employeeDailyWage
      WHERE
          startFromDate <= ?
              AND ${process.env.DB_SCHEMA}.employeeDailyWage.employeeId = ${process.env.DB_SCHEMA}.employee.id
      ORDER BY startFromDate DESC
      LIMIT 1) AS startFromDate
FROM
  ${process.env.DB_SCHEMA}.employeesTimeSheet
  Left Join                 
 ${process.env.DB_SCHEMA}.employee ON ${process.env.DB_SCHEMA}.employeesTimeSheet.employeeId = ${process.env.DB_SCHEMA}.employee.id
  left join 
${process.env.DB_SCHEMA}.salary ON ${process.env.DB_SCHEMA}.salary.employeeId = ${process.env.DB_SCHEMA}.employee.id
WHERE
  MONTH(salaryDate) = ? AND YEAR(salaryDate) = ?
GROUP BY  ${process.env.DB_SCHEMA}.employee.id
ORDER BY ${process.env.DB_SCHEMA}.employee.id asc  
`;
  const [rows] = await connection.execute(query, values);
  return rows;
}

Answer 1

all there is to know is in the error message.

3 solutions :

• remove only_full_group_by from sql_mode"
• or remove projects_managment.salary.salaryDate from your query
• or use an aggregate function on it (avg/min/max/group_concat/...)