0

I have a table called 

employee
---------
EmployeeNo(PK)
Fname
Lname
Status
Salary
Position
DeptNo(FK)

And I want to display the average salary in each department with the new column name Net_total..confused.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
P R 3 A C H 3 R
  • 55
  • 1
  • 9
  • I think this is useful,(https://stackoverflow.com/questions/13626331/how-do-i-create-column-calculated-from-another-column) – Lakshmipriya Mukundan May 31 '17 at 10:53
  • So - perhaps you should go ask your instructor for guidance, rather than asking others to do your assignment? And the confusion is obvious. Why would you name a value that represents an average as "net_total" - there is nothing "net" or "total" about a mathematical average. There is more to this problem than what you have written. – SMor May 31 '17 at 12:47

4 Answers4

1

Please try this :

ALTER TABLE employee
ADD Net_total INT NOT NULL;

UPDATE employee e
SET e.Net_total = (
SELECT AVG(e1.salary)
FROM employee e1
GROUP BY DeptNo
)

WHERE e1.DeptNo = e.DeptNo;
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Gurdyal
  • 161
  • 2
  • 2
  • 11
  • 2
    You are aware that you will have to recalculate the value in every record evertime a salary changes, or an employee is added/removed, or an employee changes from department ? – GuidoG May 31 '17 at 11:12
1

There is no need to create an extra field for this, you can calculate and show that value like this:

select e.EmployeeNo,
       e.Fname,
       e.Lname,
       e.Status,
       e.Salary,
       e.Position,
       e.DeptNo,
       (select avg(e2.Salary) from employee e2 where e2.DeptNo = e.DeptNo) as net_total
from   employee e

You can also create a view for this :

create view vwEmployee as
select e.EmployeeNo,
       e.Fname,
       e.Lname,
       e.Status,
       e.Salary,
       e.Position,
       e.DeptNo,
       (select avg(e2.Salary) from employee e2 where e2.DeptNo = e.DeptNo) as net_total
from   employee e

and now you can do

select * from vwEmployee
GuidoG
  • 11,359
  • 6
  • 44
  • 79
0

You can use avg with partition by 

Select *, Avg(Salary) over(partition by deptno) as Net_Total from employee
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
0

These are some of your options:

  1. Use DML triggers

from msdn documentation

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

So the ways is to ALTER your table to add a NULL-able column and add trigger which will update this column every time there is an INSERT or UPDATE operation

  1. Use Views

Create a view on top of this table where you add another column whose value is calculated dynamically based on your formula. You will always use the view instead of table where ever you need to reference the Net_total column

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60