Iqra Qureshi Iqra Qureshi - 6 months ago 17
SQL Question

SQL join query not working while doing SUM

I have two tables in SQL named

tblFolding
and
tblStockManagement
. In
tblFolding
I have columns
FoldingID
and
Price
. In
tblStockManagement
I have
FoldingID
(foreign key from
tblFolding
) and
Quantity
.

All I want is calculate the
Total Price
of Items Quantity available in
tblStockManagement
but
Price
comes from
tblFolding
Table. I am trying this but I don't know the exact query. please help me in this.

Answer

This will provide total price by folding id wise

SELECT TF.FoldingID, SUM(TF.Price * SM.Quantity) AS TotalPrice
FROM tblFolding TF
INNER JOIN tblStockManagement SM ON SM.FoldingID = TF.FoldingID
GROUP BY TF.FoldingID

If don't want to get the result by foldingid wise and expect the whole total price, try this:

SELECT SUM(TF.Price * SM.Quantity) AS TotalPrice
FROM tblFolding TF
INNER JOIN tblStockManagement SM ON SM.FoldingID = TF.FoldingID

UPDATE:

From your comments, what I understand is you want results according to FoldingID in tblStockManagement table. So add the WHERE clause and filter by the condition.

SELECT TF.FoldingID, SUM(TF.Price * SM.Quantity) AS TotalPrice
FROM tblFolding TF
INNER JOIN tblStockManagement SM ON SM.FoldingID = TF.FoldingID
WHERE SM.FoldingID IN (1, 2, 3, 4) -- modify the condition as per your requirement
GROUP BY TF.FoldingID
Comments