Weighted Averages

View All Public Scripts Login to Run Script

Script Name Weighted Averages
Visibility Unlisted - anyone with the share link can access
Description No description provided
Area SQL General / SQL Query
Contributor mkanugo@live.com
Created Monday May 22, 2017

Statement 1

create table employee (id number, name varchar2(50),location varchar2(50),department varchar2(50),salary number, bonus number, weight float,commission number,term number)

Table created.

Statement 2

insert into employee values (1, 'John','Boston','Accounting',1000,50,'1.18',100,32)

1 row(s) inserted.

Statement 3

insert into employee values (2, 'Mary','LA','Finance',800,150,'2.28',300,48)

1 row(s) inserted.

Statement 4

insert into employee values (3, 'Jelly','SFO','Marketing',1800,1150,'3.31',500,48)

1 row(s) inserted.

Statement 5

insert into employee values (4, 'Crab','SFO','Marketing',2800,1050,'3.31',600,32)

1 row(s) inserted.

Statement 6

insert into employee values (5, 'Peter','Boston','IT',5800,150,'1.18',0,48)

1 row(s) inserted.

Statement 7

select * from employee


ID NAME LOCATION DEPARTMENT SALARY BONUS WEIGHT COMMISSION TERM
1 John Boston Accounting 1000 50 1.18 100 32
2 Mary LA Finance 800 150 2.28 300 48
3 Jelly SFO Marketing 1800 1150 3.31 500 48
4 Crab SFO Marketing 2800 1050 3.31 600 32
5 Peter Boston IT 5800 150 1.18 0 48

5 rows selected.

ID	NAME	LOCATION	DEPARTMENT	SALARY	BONUS	WEIGHT	COMMISSION	TERM
1	John	Boston	Accounting	1000	50	1.18	100	32
2	Mary	LA	Finance	800	150	2.28	300	48
3	Jelly	SFO	Marketing	1800	1150	3.31	500	48
4	Crab	SFO	Marketing	2800	1050	3.31	600	32
5	Peter	Boston	IT	5800	150	1.18	0	48

Statement 8

select 'All' as Grain,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
union 
select 'Location' as Grain,location 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by location 
union 
select 'Department' as Grain,department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by department

ORA-01789: query block has incorrect number of result columns

More Details: https://docs.oracle.com/error-help/db/ora-01789

Statement 9

select 'All' as Grain,sum(salary*weight)/sum(weight) as Salary_Avg 
,'ALL' as Location,'ALL' as Department 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
union 
select 'Location' as Grain,location,'ALL' as Department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by location 
union 
select 'Department' as Grain,'ALL' as Location,department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by department

ORA-01790: expression must have same datatype as corresponding expression

More Details: https://docs.oracle.com/error-help/db/ora-01790

Statement 10

select 'All' as Grain,'ALL' as Location,'ALL' as Department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
union 
select 'Location' as Grain,location,'ALL' as Department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by location 
union 
select 'Department' as Grain,'ALL' as Location,department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by department


GRAIN LOCATION DEPARTMENT SALARY_AVG BONUS_AVG COMMISSION_AVG TERM_AVG
All ALL ALL 2226.82060390763765541740674955595026643 698.046181172291296625222024866785079929 394.582593250444049733570159857904085258 41.61989342806394316163410301953818827709
Department ALL Accounting 1000 50 100 32
Department ALL Finance 800 150 300 48
Department ALL IT 5800 150 0 48
Department ALL Marketing 2300 1100 550 40
Location Boston ALL 3400 100 50 40
Location LA ALL 800 150 300 48
Location SFO ALL 2300 1100 550 40

8 rows selected.

GRAIN	LOCATION	DEPARTMENT	SALARY_AVG	BONUS_AVG	COMMISSION_AVG	TERM_AVG
All	ALL	ALL	2226.82060390763765541740674955595026643	698.046181172291296625222024866785079929	394.582593250444049733570159857904085258	41.61989342806394316163410301953818827709
Department	ALL	Accounting	1000	50	100	32
Department	ALL	Finance	800	150	300	48
Department	ALL	IT	5800	150	0	48
Department	ALL	Marketing	2300	1100	550	40
Location	Boston	ALL	3400	100	50	40
Location	LA	ALL	800	150	300	48
Location	SFO	ALL	2300	1100	550	40

Statement 11

select 'All' as Grain,'ALL' as Location,'ALL' as Department 
,round(sum(salary*weight)/sum(weight),2) as Salary_Avg 
,round(sum(bonus*weight)/sum(weight),2) as Bonus_Avg 
,round(sum(commission*weight)/sum(weight),2) as Commission_Avg 
,round(sum(term*weight)/sum(weight),0) as Term_Avg 
from employee 
union 
select 'Location' as Grain,location,'ALL' as Department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by location 
union 
select 'Department' as Grain,'ALL' as Location,department 
,sum(salary*weight)/sum(weight) as Salary_Avg 
,sum(bonus*weight)/sum(weight) as Bonus_Avg 
,sum(commission*weight)/sum(weight) as Commission_Avg 
,sum(term*weight)/sum(weight) as Term_Avg 
from employee 
group by department


GRAIN LOCATION DEPARTMENT SALARY_AVG BONUS_AVG COMMISSION_AVG TERM_AVG
All ALL ALL 2226.82 698.05 394.58 42
Department ALL Accounting 1000 50 100 32
Department ALL Finance 800 150 300 48
Department ALL IT 5800 150 0 48
Department ALL Marketing 2300 1100 550 40
Location Boston ALL 3400 100 50 40
Location LA ALL 800 150 300 48
Location SFO ALL 2300 1100 550 40

8 rows selected.

GRAIN	LOCATION	DEPARTMENT	SALARY_AVG	BONUS_AVG	COMMISSION_AVG	TERM_AVG
All	ALL	ALL	2226.82	698.05	394.58	42
Department	ALL	Accounting	1000	50	100	32
Department	ALL	Finance	800	150	300	48
Department	ALL	IT	5800	150	0	48
Department	ALL	Marketing	2300	1100	550	40
Location	Boston	ALL	3400	100	50	40
Location	LA	ALL	800	150	300	48
Location	SFO	ALL	2300	1100	550	40

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database