Calculations With A Table Join

Question

I am trying to calculate here, and I'm sure it's just because I am not using the proper join. I've tried JOIN, LEFT JOIN, RIGHT JOIN, and none produce the expected result.

With my calculation I expect two rows returned (which I get), but the first one 13.60, and the second is 327.11 - INFLATED! My expected value returned is 46.73

What must I change in this query to get that output?

Create Table __ShipLineItemInfo 
(
    Id int IDENTITY(1,1) PRIMARY KEY,
    ShipId int NOT NULL,    
    ShipLineId nvarchar(max) NOT NULL
)


Create Table __ShipmentsData 
(
    Id int IDENTITY(1,1) PRIMARY KEY,
    ShipId int NOT NULL ,
    ShipCost decimal(18,2) NOT NULL,
    TrackingNumber varchar(max) NOT NULL
)

INSERT INTO __ShipLineItemInfo VALUES
(23692, 'ABC-7')
,(23708, 'ABC-1')
,(23708, 'ABC-2')
,(23708, 'ABC-3')
,(23708, 'ABC-4')
,(23708, 'ABC-5')
,(23708, 'ABC-6')
,(23708, 'ABC-8')

INSERT INTO __ShipmentsData VALUES
(23692, '13.60', '111111111111')
,(23708, '18.07', '222222222222')
,(23708, '14.33', '333333333333')
,(23708, '14.33', '444444444444')


Select
sl.shipid
,SUM(sd.ShipCost)
FROM __ShipmentsData sd
JOIN __ShipLineItemInfo sl
ON sd.ShipId = sl.ShipId
GROUP BY sl.ShipId

Answer 1

If you replace the SUM with a star and remove the GROUP BY:

Select`*
 ,SUM(sd.ShipCost)
 FROM __ShipmentsData sd
 JOIN __ShipLineItemInfo sl
 ON sd.ShipId = sl.ShipId

You will understand what is going on.

Here is a query that gives you result that you want:

SELECT sl.ShipId, sd.ShipCost
 FROM   (SELECT DISTINCT ShipId FROM __ShipLineItemInfo) AS sl
 JOIN  (SELECT ShipId, SUM(ShipCost) AS ShipCost
        FROM   __ShipmentsData
        GROUP  BY ShipId) AS sd ON sd.ShipId = sl.ShipId