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?
Using Joins –Create a sub-table containing the result of aggregated values. Using Join, use the results from the sub-table to display them with non-aggregated values.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Using Group By with Inner JoinSQL Inner Join permits us to use Group by clause along with aggregate functions to group the result set by one or more columns. Group by works conventionally with Inner Join on the final result returned after joining two or more tables.
The GROUP BY clause is normally used along with five built-in, or "aggregate" functions. These functions perform special operations on an entire table or on a set, or group, of rows rather than on each row and then return one row of values for each group.
You can't do this WHERE inc_amount=max(inc_amount)
in the where clause, either use HAVING
or do it in the condition of join, try this instead:
SELECT
e.emp_id,
e.inc_date,
t.TotalInc,
t.MaxIncAmount
FROM salary_increase AS i
INNER JOIN emp_table AS e ON i.emp_id=e.emp_id
INNER JOIN
(
SELECT
emp_id,
MAX(inc_amount) AS MaxIncAmount,
COUNT(i.inc_amount) AS TotalInc
FROM salary_increase
GROUP BY emp_id
) AS t ON e.emp_id = t.emp_id AND e.inc_amount = t.MaxIncAmount;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With