Wednesday, December 1, 2010

"GROUP BY" does not group some data - Why?


"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