Ensuring Only Distinct Product Id's are shown

Question

Consider the following partial result set which was produced from this query (derived from creating a view in ssms).

SELECT DISTINCT TOP (100) PERCENT 
    dbo.LandingHeaders.VesselId, dbo.LandingDetails.ProductId, 
    SUM(dbo.LandingDetails.Quantity) AS Quantity, 
    dbo.LandingDetails.UnitPrice
FROM
    dbo.LandingDetails 
INNER JOIN
    dbo.LandingHeaders ON dbo.LandingDetails.LandingId = dbo.LandingHeaders.LandingId 
INNER JOIN
    dbo.Vessels ON dbo.LandingHeaders.VesselId = dbo.Vessels.VesselId
GROUP BY 
    dbo.LandingHeaders.VesselId, dbo.LandingDetails.UnitPrice, 
    dbo.LandingDetails.ProductId, dbo.LandingHeaders.LandingDate1
HAVING        
   (dbo.LandingHeaders.LandingDate1 BETWEEN CONVERT(DATETIME, '2016-01-06 00:00:00', 102) 
                                    AND CONVERT(DATETIME, '2016-01-13 00:00:00', 102))
ORDER BY 
    dbo.LandingHeaders.VesselId, dbo.LandingDetails.ProductId

I want to get this to return only distinct ProductId's and the sum of their relevant quantities.

EDIT

For example in those entries where the vessel Id is 4 there are three lines where the ProductId is 22. Ideally I would like it to return the following single line,

4    22    19.1   0.4

Thus far the closest I have got is as follows.

SELECT DISTINCT 
    ld.ProductId, 
    ROUND(SUM(ld.Quantity), 2) AS Quantity,
    ld.UnitPrice, lh.VesselId 
FROM 
    LandingDetails ld 
JOIN 
    LandingHeaders lh ON ld.LandingId = lh.LandingId
GROUP BY 
    ld.ProductId, ld.UnitPrice, lh.VesselId
WHERE 
    lh.LandingDate1 BETWEEN '20160106' AND '20160113'

which obviously isn't quite close enough. I'd welcome suggestions.

Answer 1

You dont need DISTINCT, GROUP BY should do that work for you.

And WHERE go before GROUP BY

SELECT  DISTINCT ld.ProductId, 
        ROUND(SUM(ld.Quantity),2) AS Quantity,
        ld.UnitPrice, 
        lh.VesselId 
FROM LandingDetails ld 
JOIN LandingHeaders lh 
  ON ld.LandingId = lh.LandingId
WHERE lh.LandingDate1 BETWEEN '20160106' AND '20160113'
GROUP BY ld.ProductId, ld.UnitPrice, lh.VesselId