SQL Select Statment SUM Calculation

Transact-SQL
1

I got wrong sum amount in my query. I am not sure why, please help. Thanks in Advance!
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

2

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

3

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

6

please find the SQL

does not work

please let me know

:slight_smile:
:slight_smile:

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