Click here to Skip to main content
15,875,568 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
I have a table like this

SQL
Create table Employee
(
EmpID	int,
FirstName	varchar(50),
LastName	varchar(50),
Salary	int,
Address	varchar(100)
)

--Insert record:-
SQL
Insert into Employee values(1,'Hem Raj','Thakur',12000.00,'Mandi H.P.')
Insert into Employee values(2,'Khem Raj','Thakur',12500.00,'Bilas Pur H.P.')
Insert into Employee values(3,'Karan Kumar','Thakur',15600.00,'Hamir Pur H.P.')

select * from Employee

--create function
SQL
Alter function GetSumSalary
(@Salary int
)
returns varchar(101)
as begin return(SELECT SUM(Salary) AS Salary
FROM Employee GROUP BY EmpID WITH ROLLUP)
end

I Wanna Result like this:-
EmpID FirstName LastName Salary Address
1	Hem Raj	     Thakur  12000	Mandi H.P.
2	Khem Raj	 Thakur	 12500	Bilas Pur H.P.
3	Karan Kumar	 Thakur	 15600	Hamir Pur H.P.
		     TotalSalary 40100	
--How can I call This function ia a perticular sql server query.

my query is like this
SQL
select EmpID,FirstName,LastName,Address,dbo.GetSumSalary(Salary)as Salary from Employee


Please Help Me

Thanks in advance
Er.Hem Raj Thakur
Posted
Updated 6-Aug-13 20:13pm
v3

Try this:
SQL
SELECT EmpID, FirstName, LastName, Address, Salary
FROM (
    SELECT EmpID, FirstName, LastName, Address, SUM(Salary)as Salary
    FROM Employee
    GROUP BY EmpID, FirstName, LastName, Address
    UNION ALL
    SELECT NULL AS EmpID, NULL AS FirstName, NULL AS LastName, 'Total Salary:' AS Address, SUM(Salary)as Salary
    FROM Employee
    GROUP BY EmpID, FirstName, LastName, Address
) AS T
 
Share this answer
 
Comments
Raja Sekhar S 7-Aug-13 2:55am    
Nice one..+5!
Maciej Los 7-Aug-13 3:05am    
Thank you, Raja ;)
SQL
ALTER function GetSumSalary1
()
returns varchar(101)
as begin 
declare @Return varchar(101)
    select @Return = (SELECT SUM(Salary) AS Salary FROM Employee) 
return @Return
END

SQL
select EmpID,FirstName,LastName,Address,Salary from Employee
 UNION 
Select null as EmpID,null as FirstName,null as LastName,'Total Salary' as Address, dbo.GetSumSalary1() from Employee 
 
Share this answer
 
Comments
Hem Raj Thakur 7-Aug-13 4:14am    
Thank you sir this query is helpfull to me. thank you vary much.
Sadique KT 7-Aug-13 4:57am    
welcome... plz mark rating...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900