CODEWITHSUNDEEP
sqloracle-database
rkiskk
rkiskk

Reputation: 13

Sql query for different types of employees

I have query which is currently calculate the sum of net par, gross earn and gross deduction on monthly basis, but now I want a query which calculate the same properties but in the basis of different employee types, My web application is currently using by below mentioned type of employee

EMP_TYPE  EMP_TYPE_NAME 
01        Regular Employee
04        Deputated In Employee
06        Contractual Employee
03        Outsource Employees
05        Regular Employee-MPSIDC

Now I have another table i,e EMPLOYEE_SALARY_SUMMARY which contain all the necessary data regarding salary i,e NET_PAY, GROSS_EARNING, GROSS_DEDUCTION.

Till now I have data on monthly basis, i,e For example 

December, 2018, 
total Net_Pay is 2 cr, 
total gross Earn 2.5 cr, 
total gross_deduct is 50lacs

But now I also want the data on behalf of Employee Type, Example: December 2018

Regular Employee Net_pay =50 lacs, Gross Earn= 75 lacs, gross Deduct=25 Lac
Deputed-In Employee  Net_pay =50 lacs, Gross Earn= 75 lacs, gross Deduct=25 Lac
Contractual  Employee  Net_pay =50 lacs, Gross Earn= 75 lacs, gross Deduct=25 Lac

I wrote this but I didn't get the expected output, right now I am using the case only for Regular Employee, i,e Emp_TYPE= 1

select
count(distinct ess.EMPLOYEE_ID)emp_count,
SUM(case when et.EMP_TYPE_ID = 1 then ess.NET_SALARY else 0 end )net_sal,
SUM(case when et.EMP_TYPE_ID = 1 then ess.GROSS_EARNING else 0 end )gross_earn,
SUM(case when et.EMP_TYPE_ID = 1 then ess.GROSS_DEDUCTION else 0 end )gross_deduct
from 
employee_salary_summary ess
JOIN employee e
on e.EMP_ID=ess.EMPLOYEE_ID
JOIN EMPLOYEE_TYPE et
ON et.EMP_TYPE_ID=e.EMP_TYPE_ID 
group by ess.NET_SALARY, ess.GROSS_EARNING, ess.GROSS_DEDUCTION;

Also suggest me the efficient way to write the same query, so it never fails in any case.

Current output:

emp_count net_pay gross_earn gross_deduct
    1    26862    30643         3781
    1    37229    42361         5132
    1    66304    75222         8918
    1    19124    22300         3176
    2    93144    105840        12696
    4    93176    106444        13268
    1    0         0              0
    1    38434    43722         5288
    1    0         0              0
    2    81996    93240         11244

It also fetches the other employee types, like you can see in the output, Emp_Type is not limited to 1, query also fetches the other employee_types like 2 and 4.

Expected output should be

emp_count Employee type    net_pay      gross_earn     gross_deduct
  51       Regular Emp     15,00000     17,000000        2,00000
  26       Deputed-In       8,00000       1,00000        7,00000
  29       contractual emp  6,00000       2,00000        4,00000

Upvotes: 0

Views: 580

Answers (1)

APC
APC

Reputation: 146249

To display data by Employee Type you need to include EMP_TYPE_NAME in the projection of the query. To get the correct totals you need to GROUP BY the columns you're not aggregating.

You haven't included any clues about how to derive the pay month so I've left it out but you may want to add it to the WHERE clause (or maybe the projection, but it's not in your expected output):

select
    et.EMP_TYPE_NAME
    count(distinct ess.EMPLOYEE_ID)emp_count,
    SUM(ess.NET_SALARY) net_sal,
    SUM(ess.GROSS_EARNING)gross_earn,
    SUM(ess.GROSS_DEDUCTION)gross_deduct
from 
employee_salary_summary ess
JOIN employee e
on e.EMP_ID=ess.EMPLOYEE_ID
JOIN EMPLOYEE_TYPE et
ON et.EMP_TYPE_ID=e.EMP_TYPE_ID 
/* where ess.pay_month = '2018-DEC' ??? */
group by et.EMP_TYPE_NAME
;

Upvotes: 1

Related Questions