Skip to Main Content

avg of all emp count

User_7PZDEMay 28 2020 — edited May 28 2020

I have table with svl2_name with the combination of Grad, Country, Region with avg Count_emp in other SVL2_Name and Update the Tot_Avg_Grad Column

DROP TABLE GRADE_S1;

create table GRADE_S1

(

SVL2_NAME varchar2(29),

GRADE    NUMBER,

COUNTRY  VARCHAR2(20),

REGION   varchar2(20),

COUNT_EMP NUMBER,

TOT_AVG_GRAD FLOAT

);

insert into GRADE_S1 values('TY',10,'CANADA','AMERICAS',3,null);

insert into GRADE_S1 values('TY',10,'CHINA','APJC',4,null);

insert into GRADE_S1 values('TY',9,'CHINA','APJC',6,null);

insert into GRADE_S1 values('TY',12,'FRANCE','EMEA',8,null);

insert into GRADE_S1 values('Anuj',10,'CANADA','AMERICAS',4,null);

insert into GRADE_S1 values('Anuj',10,'CHINA','APJC',6,null);

insert into GRADE_S1 values('Anuj',12,'FRANCE','EMEA',2,null);

insert into GRADE_S1 values('kumar',10,'CANADA','AMERICAS',4,null);

insert into GRADE_S1 values('kumar',10,'CHINA','APJC',4,null);

insert into GRADE_S1 values('kumar',9,'CHINA','APJC',6,null);

insert into GRADE_S1 values('kumar',12,'FRANCE','EMEA',6,null);

Need a summary table as below.

Name--Grad---Country--------Region -----Count_emp---Tot_Avg_Emp

TY----- 10----- CANADA----- AMERICAS -----3----------4

TY----- 10----- CHINA -----APJC -----4---------------5

TY----- 9----- CHINA -----APJC -----6---------------3

TY----- 12----- FRANCE -----EMEA -----8---------------4

i am thinking to create procedure like

Step 1:

--FOR Loop

select * from GRADE_S1 where SVL2_NAME='TY';

Step 2:

select AVG(COUNT_EMP)  INTO V_AVG_EMP

  from GRADE_S1 where

GRAD=INX.GRAD AND COUNTRY=INX.COUNTRY AND REGION = INX.REGION AND SVL2_NAME <> INX.SVL2_NAME

Step 3:

update GRADE_S1 set

  TOT_AVG_GRAD =V_AVG_EMP

  WHERE SVL2_NAME = INX.SVL2_NAME AND GRAD=INX.GRAD AND COUNTRY=INX.COUNTRY AND REGION = INX.REGION

or is there any method...?

Thanks

Comments
Post Details
Added on May 28 2020
8 comments
85 views