SQL select query using joins, group by and aggregate functions

Viewed 32115

I have two tables with the following fields

emp_table: emp_id, emp_name
salary_increase: emp_id, inc_date, inc_amount

I am required to write a query which gives the employee details, the number of times an employee has received a salary increase, the value of the maximum increase amount and the date of that increase. Here is what i have so far:

SELECT e.*, count(i.inc_amount), max(i.inc_amount)
FROM salary_increase AS i
RIGHT JOIN emp_table AS e
ON i.emp_id=e.emp_id
GROUP BY e.emp_id;

this correctly gives all the requirements apart from the date on which the maximum increase was awarded. I have tried the following with no success:

SELECT e.*, count(i.inc_amount), max(inc_amount), t.inc_date
FROM salary_increase AS i
RIGHT JOIN emp_table AS e
ON i.emp_id=e.emp_id
RIGHT JOIN
    (
    SELECT emp_id, inc_date FROM salary_increase
    WHERE inc_amount=max(inc_amount) GROUP BY emp_id
    ) AS t
ON e.emp_id=t.emp_id
GROUP BY e.emp_id;

this gives an error 'Invalid use of group function'. Does anyone know what i'm doing wrong?

1 Answers
Related