How to find total salary of each employee IN SQL??

I have a department table and i find out to total salary of each employee by department Id.

DeptId       Name      Salary
1            AA        2000 
1            AA       3000 
2            BB       1000

3            cc        1000
3            cc        4000
 

 sql query


SELECT E.DeptId,ISNULL(SUM(ES.Salary),0) AS Amt FROM Employee E
LEFT OUTER JOIN Employee ES ON E.DeptId=ES.DeptId
GROUP BY E.DeptId

 Output

DeptId             Salary
1                   5000 
2                   1000

3                   5000

How to Convert Indian Rupee to Word format

In this Articles ,I would like to show  simple function to Convert  Indian Money to word format in Indian money .
Pass the input string in function and get the value in word according to Indian rupee.

For example the input value 9,00,000  (Indian Money)  The output as follow:

Input value: 9,00,000
Output : 9 Lacs
Source Code

CREATE FUNCTION [dbo].[AmountToWords]

(
@TOTALAMOUNT varchar(max)
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @AMOUNTWORD varchar(500)
DECLARE @LENGTH VARCHAR(200)=0
DECLARE @SECONDWORD VARCHAR(200)
SET @LENGTH=(SELECT LEN(@TOTALAMOUNT))
IF(@LENGTH=4)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Thousand'
END
ELSE IF(@LENGTH=5)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + 'Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Thousand'
END
ELSE IF(@LENGTH=6)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Lacs'
END
IF(@LENGTH=7)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Lacs'
END
ELSE IF(@LENGTH=8)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Crore'
END
ELSE IF(@LENGTH=9)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Crore'  
END
ELSE IF(@LENGTH=10)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Arab'  
END
ELSE IF(@LENGTH=11)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Arab'
END
RETURN @AMOUNTWORD
END

Output

Select dbo.AmountToWords('900000') as Money

Output : 9 Lacs