I am trying to display a total quantity of products, their weight and price. The results come from 4 different tables.
When I do this without join then I get proper values but when I use Join then I get double values on all outputs.
Without join I can’t achieve this as these results need to be all in one table.
I tried not using Join and just including those tables in ‘FROM’ but that thrown me errors about issues converting to numeric. I also tried Union but didn’t work.
When using no join and not trying to display all the values, I get the desired output but with missing columns that I missed out on purpose to test this.
SELECT PriceListTest.Description, COUNT(ItemCode) AS Quantity, SUM(Weight) AS 'Weight', Item.Pieces, PriceListTest.Price, CAST(SUM( PriceListTest.Price * Weight) as DECIMAL(10,2)) as 'Unit Price', CAST(SUM(PriceListTest.Price * Weight) as DECIMAL(10,2)) AS 'Nett Amount' FROM StockItems INNER JOIN PriceListTest ON StockItems.ItemCode = PriceListTest.Description INNER JOIN Item ON StockItems.ItemCode = Item.ShortCode WHERE Barcode IN (SELECT DISTINCT Barcode FROM StockOuttbl WHERE ContainedID = 'isr5063' AND Status ='' GROUP BY Barcode) AND PriceListTest.CustomerID = (SELECT DISTINCT CustomerID From Customerstbl WHERE CustomerID ='1') GROUP BY PriceListTest.Description, Item.Pieces, PriceListTest.Price;
Status in this case is empty, so that’s not the issue
I getting these values:
Description Quantity Weight Pieces Price Unit Price Nett Amount Product1 52 20242 0 1.23 24897.66 24897.66 Product2 50 17206 0 1.23 21163.38 21163.38
This is the output I am looking:
Description Quantity Weight Pieces Price Unit Price Nett Amount Product1 26 10121 0 1.23 12448.83 12448.83 Product2 25 8603 0 1.23 10581.69 10581.69
When I don’t use the PriceListTest in Join then I don’t get the doubles, but then it’s not exactly what I am looking for.
Description Quantity Weight Pieces Product1 26 10121 0 Product2 25 8603 0
I’d appreciate any help with this. Thanks in advance!