"GROUP BY" clause (tested in PostgreSQL) does not group rows by on-the-fly temporary columns included in SELECT statement. For example, the below SQL will not group properly since 'manager_name' column is included in 'GROUP BY'. But, removing it from 'GROUP BY' will work properly.
Erroneous sql:
SELECT
emp.name,
salary.year,
SUM(salary.amount),
'Ela' as manager_name -- On the fly temporary column
FROM
emp
JOIN
salary ON salary.emp_id = emp.id
GROUP BY
emp.name,
salary.year,
manager_name -- <-- Here is the problem! This should be removed.
ORDER BY
emp.name,
salary.year;
Corrected SQL:
SELECT
emp.name,
salary.year,
SUM(salary.amount),
'Ela' as manager_name
FROM
emp
JOIN
salary ON salary.emp_id = emp.id
GROUP BY
emp.name,
salary.year
ORDER BY
emp.name,
salary.year;
No comments:
Post a Comment