CODEWITHSUNDEEP
sql
Avacay
Avacay

Reputation: 163

Sql individual SUM

I'm currently collecting data from individual tables. I'm supposed to get my sum (amount*weight) For each shipment. However it gives me the overall sum*weight, which means I get the same result all the way down.

Here is the code I carried out: 

SELECT SUM(Weight*Amount), Arrival_Date 
FROM Specifications, Shipment, `Product shipment` 
WHERE Specifications.Product_Code = `Product shipment`.Product_Code 
AND `Product shipment`.Shipment_ID = `product Shipment`.shipment_ID 
GROUP BY Arrival_Date

What am I missing?

Upvotes: 0

Views: 63

Answers (3)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186813

Extend GROUP BY of your query:

  SELECT SUM(Weight * Amount), 
         Arrival_Date 
    FROM Specifications, 
         Shipment, 
        `Product shipment` 
   WHERE Specifications.Product_Code = `Product shipment`.Product_Code 
     AND `Product shipment`.Shipment_ID = `product Shipment`.shipment_ID 
GROUP BY `Product shipment`.Shipment_ID, -- <- try add this
         Arrival_Date

Upvotes: 1

Haji
Haji

Reputation: 2077

Your are not grouped by shipment_ID to get sum for each Shipment_ID

SELECT SUM(Weight*Amount), Arrival_Date 
  FROM Specifications, Shipment, `Product shipment` 
WHERE Specifications.Product_Code = `Product shipment`.Product_Code 
  AND `Product shipment`.Shipment_ID = `product Shipment`.shipment_ID 
  GROUP BY Shipment`.shipment_ID ,Arrival_Date

Upvotes: 0

schmop
schmop

Reputation: 1440

You should group by shipment_ID, not Arrival_Date. I'm not sure but maybe shipment_ID needs to appear in the SELECT clause for this to work.

Upvotes: 0

Related Questions