CODEWITHSUNDEEP
sqlsql-server
mjordan
mjordan

Reputation: 369

SQL Query Join two Queries with Sum()

I know this is simple for you sql people, but I am trying to combine two simple queries into one. It's probably obvious from the code that job.ActiveJob.ID = job.EstimateTbl.ID_Job.

I'm trying to get: id, JobName, sum(itemAmount)

Thanks for your help.

SELECT a.id, a.JobName
FROM job.ActiveJobsTbl AS a
WHERE a.ID = '100' 

SELECT SUM(itemAmount)
FROM job.EstimateTbl
WHERE ID_Job = '100'

Upvotes: 2

Views: 172

Answers (4)

Rima
Rima

Reputation: 1455

using LEFT JOIN

SELECT a.id, a.JobName,EstimateTbl.itemAmount
FROM job.ActiveJobsTbl AS a
left join
( 
    SELECT SUM(itemAmount) itemAmount,ID_Job
    FROM job.EstimateTbl
    group by ID_Job
) EstimateTbl on  EstimateTbl.ID_Job = a.ID
WHERE a.ID = '100'

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Try this:

SELECT A.id, A.JobName, SUM(E.itemAmount) AS Amount 
FROM job.ActiveJobsTbl AS A
INNER JOIN job.EstimateTbl E ON E.ID_Job = A.ID 
WHERE A.ID = '100'
GROUP BY A.id, A.JobName;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use APPLY :

select a.id, a.JobName, t.itemAmount 
from job.ActiveJobsTbl AS a OUTER APPLY
     (SELECT SUM(t.itemAmount) AS itemAmount
      FROM job.EstimateTbl AS t
      WHERE t.ID_Job = a.ID
     ) t
WHERE a.ID = 100;

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

You can JOIN the tables by the ON E.ID_Job = A.ID and get the values in SELECT with GROUP BY

SELECT A.id, A.JobName, SUM(E.itemAmount) AS Amount 
FROM job.ActiveJobsTbl AS A
INNER JOIN job.EstimateTbl E ON E.ID_Job = A.ID 
WHERE A.ID = '100'  
GROUP BY A.id, A.JobName;

Upvotes: 5

Related Questions