Sum the fields in SELECT Statement

How I can make sum for 2 sums in SELECT statement as

SELECT Distinct E.fld_Employee_CODE, E.FROM_PERIOD, E.TO_PERIOD,

(SELECT Sum(I.ALW_DED_AMOUNT) 
FROM tbl_EmployeeMonthSalary AS I 
Where I.fld_Employee_CODE = E.fld_Employee_CODE and E.FROM_PERIOD = E.FROM_PERIOD and I.TO_PERIOD = E.TO_PERIOD
and I.ALW_DED_CODE = "666") As [Basic Salary],
(SELECT Sum(I.ALW_DED_AMOUNT) 
FROM tbl_EmployeeMonthSalary AS I 
Where I.fld_Employee_CODE = E.fld_Employee_CODE and E.FROM_PERIOD = E.FROM_PERIOD and I.TO_PERIOD = E.TO_PERIOD
and I.ALW_DED_CODE = "777") As [Accomadation],
(SELECT Sum(I.ALW_DED_AMOUNT) 
FROM tbl_EmployeeMonthSalary AS I 
Where I.fld_Employee_CODE = E.fld_Employee_CODE and E.FROM_PERIOD = E.FROM_PERIOD and I.TO_PERIOD = E.TO_PERIOD
and I.ALW_DED_CODE = "888") As [Electrisity],
(SELECT Sum(I.ALW_DED_AMOUNT) 
FROM tbl_EmployeeMonthSalary AS I 
Where I.fld_Employee_CODE = E.fld_Employee_CODE and E.FROM_PERIOD = E.FROM_PERIOD and I.TO_PERIOD = E.TO_PERIOD
and I.ALW_DED_CODE = "999") As [Water],
[Basic Salary]+[Accomadation]+[Electrisity]+[Water] As [Total Salary]

FROM tbl_EmployeeMonthSalary AS E 

Open in new window


In above example you can see
(SELECT Sum(I.ALW_DED_AMOUNT) 
FROM tbl_EmployeeMonthSalary AS I 
Where I.fld_Employee_CODE = E.fld_Employee_CODE and E.FROM_PERIOD = E.FROM_PERIOD and I.TO_PERIOD = E.TO_PERIOD
and I.ALW_DED_CODE = "666") As [Basic Salary],
(SELECT Sum(I.ALW_DED_AMOUNT) 
FROM tbl_EmployeeMonthSalary AS I 
Where I.fld_Employee_CODE = E.fld_Employee_CODE and E.FROM_PERIOD = E.FROM_PERIOD and I.TO_PERIOD = E.TO_PERIOD
and I.ALW_DED_CODE = "777") As [Accomadation],

Open in new window


I'm using MS Access

How I can sum the [Basic Salary] and [Accomadation] ?
egovernmentAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Beyond just [Basic Salary]+[Accomadation] ?
0
 
Dale FyeCommented:
Are you just trying to get the Basic Salary, Accomadation, Electrisity, Water, and Total Salary for each employee, time period combination?  If so, try:

SELECT E.fld_Employee_Code,
            E.From_Period,
            E.To_Period,
            Sum(iif(E.ASL_DED_CODe] = "666", E.ALW_DED_AMOUNT, 0))  As [Basic Salary],
            Sum(iif(E.ASL_DED_CODe] = "777", E.ALW_DED_AMOUNT, 0))  As [Accomadation],
            Sum(iif(E.ASL_DED_CODe] = "888", E.ALW_DED_AMOUNT, 0))  As [Electrisity],
            Sum(iif(E.ASL_DED_CODe] = "999", E.ALW_DED_AMOUNT, 0))  As [Water],
            SUM([Basic Salary]+[Accomadation]+[Electrisity]+[Water]) As [Total Salary]
FROM tbl_EmployeeMonthSalary AS E
GROUP BY SELECT E.fld_Employee_Code, E.From_Period, E.To_Period
0
 
Dale FyeCommented:
After you run your version (time it to see how long it takes), you might want to try my version.  Your version is going to take forever to run, compared to my recommendation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.