# How to use JOIN and SUM function to Calculate the total of query returned column?

I ran bellowing query;

Query:

``````SELECT  j.jobId,
j.productId,
p.productUnitPrice
FROM    JobRequiredProducts J
JOIN    Product p
ON    p.productId = j.productId
WHERE   j.jobId = 1
ORDER BY j.jobId
``````

And got bellowing result;

Query result:

``````jobId | productId | unitPrice
_____________________________
1   |     4     |   175.99
1   |     5     |   100.00
1   |     6     |   125.00
``````

Now, I want to calculate all unitPrice to get the total. the result I expect is;

Expecting result:

``````jobId | productId | Total
____________________________
1   |     4     |   175.99
1   |     5     |   100.00
1   |     6     |   125.00
null |    null   |   400.99
``````

Therefor bellowing is the query I tried;

Query I tried:

``````SELECT  j.jobId,
j.productId,
SUM(p.productUnitPrice) AS 'Total'
FROM    JobRequiredProducts J
JOIN    Product p
ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY j.jobId,j.productId WITH ROLLUP
``````

But it didn't give me the result I expected, where I went wrong and what I gotta do to get the result I expect?

You can use grouping set also

``````declare @JobRequiredProducts table (jobId int, productId int);
declare @Product table(productId int, productUnitPrice decimal(8,2))

insert into @JobRequiredProducts(jobId,productId)
values(1,4),(1,5),(1,6);

insert into @Product(productId, productUnitPrice)
values(4,175.99),(5 ,100.00),( 6 , 125.00)

SELECT  j.jobId,
j.productId,
SUM(p.productUnitPrice) AS 'Total'
FROM    @JobRequiredProducts J
JOIN    @Product p
ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY  ROLLUP ((j.jobId, j.productId));
``````

with grouping sets

``````SELECT  j.jobId,
j.productId,
SUM(p.productUnitPrice) AS 'Total'
FROM    @JobRequiredProducts J
JOIN    @Product p
ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY  GROUPING SETS( (j.jobId, j.productId),());
``````

output:

``````jobId   productId   Total
1       4           175.99
1       5           100.00
1       6           125.00
NULL    NULL        400.99
``````

dbfiddle here