egovernment
asked on
Sum the fields in SELECT Statement
How I can make sum for 2 sums in SELECT statement as
In above example you can see
I'm using MS Access
How I can sum the [Basic Salary] and [Accomadation] ?
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
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],
I'm using MS Access
How I can sum the [Basic Salary] and [Accomadation] ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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]+[El
FROM tbl_EmployeeMonthSalary AS E
GROUP BY SELECT E.fld_Employee_Code, E.From_Period, E.To_Period