Question 240371 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Group by and get one of column value in group by based on certain condition of other column in same table

I have below PostgreSQL table, which is small sample of huge table

employee

emp_id | salary | rating | increment_year
--------------------------------------
28      3000      1         2023
25      2000      2         2023
30      1500      1         2021
28      1000      3         2022
32      1200      1         2023
23      1550      1         2023
30      2500      3         2022        
30      3000      2         2023

I want make a PostgreSQL query which does
group by emp_id and returns emp_id, sum(salary), get rating value based on latest increment_year, latest increment_year that was selected to get rating value

Example query result sample

emp_id | sum_salary | selected_rating_on_latest_increment_year | selected_increment_year_to_get_rating
------------------------------------------------------------------------------------------------------
28       4000         1                                         2023

value goes on for every emp_id

I have to use group by due to certain pre existing condition. So Doing it by group by, is something mandatory for me, if its possible.

Thanks in advance!

Tags:

2

Answers


  1. 0 
    SELECT DISTINCT
       emp_id,
       SUM(salary) OVER (PARTITION BY emp_id),
       FIRST_VALUE(rating) OVER (PARTITION BY emp_id ORDER BY increment_year DESC),
       MAX(increment_year) OVER (PARTITION BY emp_id)
FROM employee;

    I have to use group by due to certain pre existing conditions. Can you please help me do it by group by. – young_minds1

    SELECT e2.*, e1.rating 
FROM employee e1
JOIN ( SELECT emp_id,
              SUM(salary) salary,
              MAX(increment_year) increment_year
       FROM employee
       GROUP BY 1
       ) e2 USING (emp_id, increment_year)
    Login or Signup to reply.
  2. 0

    As a verbose alternative you may use a scalar subquery for selected_rating_on_latest_increment_year value instead of a JOIN. The rest would come from the t CTE that does have a group by.

    with t as
(
  select emp_id, sum(salary) sum_salary, max(increment_year) increment_year
  from employee
  group by emp_id
) select emp_id, sum_salary, 
         (
           select rating from employee
           where emp_id = t.emp_id and increment_year = t.increment_year
         ) selected_rating_on_latest_increment_year,
         increment_year
 from t;

    DB-Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search