Link to home
Start Free TrialLog in
Avatar of egovernment
egovernmentFlag for Afghanistan

asked on

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] ?
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.