3

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?

8

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.