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

Question

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?

Answer 1

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

• Aaby: Please give me an Idea to this question too dba.stackexchange.com/questions/241303/… @Sabin Bio