Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL select query using joins, group by and aggregate functions

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?

like image 354
StickyCube Avatar asked Dec 15 '13 13:12

StickyCube


People also ask

Can we use aggregate function in JOIN query?

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.

Can we use GROUP BY with aggregate function in SQL?

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.

Can we use GROUP BY and join together in SQL?

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.

What happens when you use an aggregate function with a GROUP BY clause?

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.


1 Answers

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;
like image 192
Mahmoud Gamal Avatar answered Nov 14 '22 17:11

Mahmoud Gamal