How do I refine this query to return individual product id's with their quantity summed

Question

this is in reality a follow up from this question.

The following query produces the results shown. How do I refine it so that it returns single rows for each product Id with their respective quantities summed.

    SELECT DISTINCT ld.ProductId, ld.Quantity,ld.UnitPrice ,
  STUFF((SELECT ',' + CAST(lh1.LandingId AS VARCHAR) FROM dbo.LandingHeaders lh1 WHERE (lh1.LandingDate  BETWEEN '20171101' AND '20171107') AND (lh1.VesselOwnerId = 42
  ) FOR XML PATH('')) ,1,1,'') AS LandingIds

  FROM LandingHeaders lh 
  JOIN LandingDetails ld ON lh.LandingId = ld.LandingId
  WHERE (lh.LandingDate BETWEEN '20171101' AND '20171107') AND (lh.VesselOwnerId = 42)
  GROUP BY ld.ProductId,lh.LandingId,ld.Quantity,ld.UnitPrice

wrap your query as a subquery, in the main query do the sums and group by — user1443098, May 08 '18 at 18:08
Change GROUP BY clause accoordingly and add the aggregation needed. — Serg, May 08 '18 at 18:09

score 1 · Accepted Answer · answered May 08 '18 at 18:11

You need to SUM the quantity column, and not GROUP BY it. Also, please always give the explicit length when using varchar:

SELECT  ld.ProductId, 
        SUM(ld.Quantity) Quantity,
        ld.UnitPrice,
        STUFF(( SELECT ',' + CAST(lh1.LandingId AS VARCHAR(20)) 
                FROM dbo.LandingHeaders lh1 
                WHERE lh1.LandingDate  BETWEEN '20171101' AND '20171107' 
                AND lh1.VesselOwnerId = 42 FOR XML PATH('')) ,1,1,'') AS LandingIds
FROM LandingHeaders lh 
INNER JOIN LandingDetails ld 
    ON lh.LandingId = ld.LandingId
WHERE lh.LandingDate BETWEEN '20171101' AND '20171107' 
AND lh.VesselOwnerId = 42
GROUP BY ld.ProductId,
         ld.UnitPrice
;

Many thanks for the example. I take your point about the undefined varchar, it's not something I'd normally leave undefined. — Dom Sinclair, May 08 '18 at 18:17

score 0 · Answer 2 · answered May 08 '18 at 18:10

0

Try this:

SELECT productId, SUM(quantity), MAX(unitprice)
FROM
(<your query>) as t
GROUP BY productId

answered May 08 '18 at 18:10

Rahul Jain

1,319
7
16

How do I refine this query to return individual product id's with their quantity summed

2 Answers2