# SQL Select Statment SUM Calculation

Here's my query
select a.EMPLOYEE, a.LAST_NAME, a.FIRST_NAME, a.EMP_STATUS, a.JOB_CODE, a.PAY_RATE, sum(b.WAGE_AMOUNT) as TotalAmount
from EMPLOYEE a INNER JOIN QUARTWAGE b ON a.EMPLOYEE = b.EMPLOYEE
where a.NBR_FTE = '0.5' --and EMP_STATUS not in('DD', 'RS', 'RC')
And a.DATE_HIRED <'1/1/2018'
And (a.TERM_DATE >'1/1/2017' or a.TERM_DATE = '1/01/1753')
group by a.EMPLOYEE, a.LAST_NAME, a.FIRST_NAME, a.EMP_STATUS, a.JOB_CODE, a.PAY_RATE
order by a.EMP_STATUS

OUTPUT
EMPLOYEE LAST_NAME FIRST_NAME EMP_STATUS JOB_CODE PAY_RATE TotalAmount
3463 Goodall Sim A0 1246 48.3535 403478.19

When I do single query like this and I got correct sum
select EMPLOYEE, sum(WAGE_AMOUNT) as TotalAmount from QUARTWAGE where EMPLOYEE =3463 AND PAYROLL_YEAR = '2017'
group by EMPLOYEE
OUTPUT
EMPLOYEE TotalAmount
3463 34828.45

what you have to do is

take this ...

SQL
``````SELECT employee,
Sum(wage_amount) AS TotalAmount
FROM   quartwage
WHERE  employee = 3463
AND payroll_year = '2017'
``````

and join to this

other SQL
``````SELECT a.employee,
a.last_name,
a.first_name,
a.emp_status,
a.job_code,
a.pay_rate
FROM   employee a
INNER JOIN quartwage b
ON a.employee = b.employee
WHERE  a.nbr_fte = '0.5' --and EMP_STATUS not in('DD', 'RS', 'RC')
AND a.date_hired < '1/1/2018'
AND ( a.term_date > '1/1/2017'
OR a.term_date = '1/01/1753' )
ORDER  BY a.emp_status
``````

on employee

I am not sure how to join in query. Do you have write sub query to join? I need help. thanks

does not work

SQL
``````SELECT a.*,b.TotalAmount FROM
(
SELECT a.employee,
a.last_name,
a.first_name,
a.emp_status,
a.job_code,
a.pay_rate
WHERE  a.nbr_fte = '0.5'
AND a.date_hired < '1/1/2018'
AND ( a.term_date > '1/1/2017'
OR a.term_date = '1/01/1753' )
ORDER  BY a.emp_status
) a
join
(
SELECT employee,
Sum(wage_amount) AS TotalAmount
FROM   quartwage
WHERE  employee = 3463
AND payroll_year = '2017'
GROUP  BY employee
)
b
on a.employee = b.employee
``````