CODEWITHSUNDEEP
sqlsql-server
javad
javad

Reputation: 835

Calculate the credit amount the employee

I'll calculate the amount of employee performance: 

select Job_Id, Sum(money1) as m1 from T1 where Job_Id='8'
group by Job_Id

Then I calculated the amount of his salary(in other Table = T2): 

select Job_Id, Sum(money2) as m2 from T2 where Job_Id='8'
group by Job_Id

Now, I will get the following: m1 - m2

How?

Upvotes: 0

Views: 73

Answers (3)

javad
javad

Reputation: 835

I found it:

select (select Sum(money1) from T1 where Job_Id='8') - 
       (select Sum(money2) from T2 where Job_Id='8')

Upvotes: 0

mohan111
mohan111

Reputation: 8865

WITH T1 AS 
( 
  SELECT 

    SUM(Money1) as Total 
  FROM M1 
) 
SELECT SUM(Money1 - Money2)
FROM T1 INNER JOIN M2 
ON M2.Job_ID = T1.Job_ID 
where M2.JOB_ID = 8

Upvotes: 2

CodeBird
CodeBird

Reputation: 3858

I think this will do what you want:

SELECT job, m1-m2 FROM 
(
SELECT T1.Job_Id AS job, Sum(money1) as m1, Sum(money2) as m2 
FROM T1 LEFT JOIN T2 
ON T1.Job_id=T2.Job_id 
WHERE T1.Job_Id='8'
group by T1.Job_Id
)

Upvotes: 2

Related Questions