0

I'm trying to create a Table Function Total_Salary_By_Dept that returns a table Total_Salary_By_Dept_Table with 5 column:

Dept_No (Int)
Dept_Name (Char(30))
COUNT_Emp (INT)
SUM_Salary (INT)
AVE_Salary (INT)

All with company database;

This is what I have so far, not sure if did I right:

Use Company
GO


CREATE FUNCTION Total_Salary_By_Dept ()
RETURNS @Total_Salary_By_Dept_Table TABLE
  (
   Dept_No int,
Dept_name char(30),
   COUNT_Emp int,
   Sum_Salary int,
   AVE_Salary int
  )
AS
BEGIN
INSERT INTO @Total_Salary_By_Dept_Table
      Select e.Dept_No, d.Dept_Name, Count(e.Dept_No), SUM(e.salary) as Sum_Salary,AVG(e.salary) as AVE_Salary
from EMPLOYEE e
join DEPARTMENT d ON e.dno = d.dnumber
Group by e.Dept_No, d.Dept_Name
  RETURN
END

but I get this error output:

Msg 207, Level 16, State 1, Procedure Total_Salary_By_Dept, Line 18
Invalid column name 'Dept_No'.
Msg 207, Level 16, State 1, Procedure Total_Salary_By_Dept, Line 18
Invalid column name 'Dept_Name'.
Msg 207, Level 16, State 1, Procedure Total_Salary_By_Dept, Line 15
Invalid column name 'Dept_No'.
Msg 207, Level 16, State 1, Procedure Total_Salary_By_Dept, Line 15
Invalid column name 'Dept_Name'.
Msg 207, Level 16, State 1, Procedure Total_Salary_By_Dept, Line 15
Invalid column name 'Dept_No'.

Did I type anything wrong? also did I did my function the right way? Please give me advices!

liam chang
  • 1
  • 2

1 Answers1

0

Take a look to the examples on this link.

https://msdn.microsoft.com/en-us/library/ms186755.aspx

JCA
  • 16
  • 2