Sum of Salaries

       

Write an SQL query to fetch the sum of all salaries in the second year, for all employees who meet the following conditions:-

• The salaries value in the first year is same as atleast one other employee.
• Employees who are not present in same floor and department. (i.e, floor, dept attribute pairs must be unique.

Round the sum of second year salaries to 2 decimal places.

Input :-

Salary table :-
ID is the Primary Key column for the Salary table.

ID	Year1	Year2	Floor	Dept
1	50000	35000	1	1
2	75000	75000	3	3
3	50000	80000	3	3
4	50000	15000	5	5

Output :-

Year2

50000.00

Query :-

with table1 as 
(
    select year1 from Salary
    group by year1
    having count(year1)>1
),
table2 as 
(
    select floor, dept from Salary
    group by floor, dept
    having count(*)=1
)
select round(sum(year2),2) Year2
from Salary
where year1 in (select * from table1) and
(floor,dept) in (select * from table2);

Explanation : - 

This query is written in SQL and performs a calculation based on the data in a table called ‘Salary’. Let's break it down step by step:

• The query begins by defining a common table expression (CTE) named "table1". It selects the distinct salary values of the column ‘year1’ from the "Salary" table and groups them by year. The ‘Having’ clause ensures that only those salaries with more than one occurrence are included in the result.
• Next, another CTE named "table2" is defined. It selects the distinct values of the ‘floor’ and ‘dept’ columns from the "Salary" table and groups them together. The ’Having’ clause specifies that only those combinations with exactly one occurrence should be included.
• The main part of the query selects the sum of the salaries from the column ‘year2’ from the "Salary" table, rounding the result to two decimal places. It applies two conditions in the ‘Where’ clause:

1. The first condition specifies that the value in the ‘year1’ column should be present in the result of the subquery defined by ‘select * from table1’. This ensures that only salaries with more than one occurrence, as identified in "table1", are considered.
2. The second condition specifies that the combination of ‘floor’ and ‘department’ should be present in the result of the subquery defined by ‘select * from table2’. This ensures that only floor-department combinations with exactly one occurrence, as identified in "table2", are considered.

In summary, the query calculates the sum of the ‘year2’ column from the "Salary" table, rounded to two decimal places, for the specific years and floor-department combinations that meet the criteria mentioned above.