March 6, 2011 at 12:50 am
i'm new in sql server 2005 plz help me i try to sum totalquantities both receiving an Sales and subtract it in totalquantity
plz help me solve this asap
SELECT tblStockMaster.SmName AS StockMaster, SUM(isnull(tblSalesD.TotalQuantity,0)) AS sum1
FROM tblStockMaster LEFT JOIN tblSalesD ON tblSalesD.StockControl=tblStockMaster.SmName GROUP BY tblStockMaster.SmName
left outer join
SELECT tblStockMaster.SmName AS StockMaster, SUM(tblReceivingD.TotalQuantity) AS sum2
FROM tblStockMaster LEFT JOIN tblReceivingD ON tblReceivingD.StockControlR=tblStockMaster.SmName
GROUP BY tblStockMaster.SmName
March 6, 2011 at 1:48 am
Please help us help you by providing ready to use test data, including table def and sample data (especially if your request is "urgent"...). For the best way on how to do it please read and follow the first article referenced in my signature.
Also, please includde your expected result based on those sample as well as what you've tried so far.
March 6, 2011 at 4:09 am
Sorry sir i post it wrong. The zip below show the data and the query,
i try to sum the different table the table recieving and purchase and subtract the quantity!
in one totalquantity im using sql server and connect to vb6
the problem is how can combine them and subtract it ! sorry its my first time in sql server i try to figure it out hope
if i post i wrong pleas correct me again
SELECT tblStockMaster.SmName AS StockMaster, SUM(isnull(tblReceivingD.TotalQuantity,0)) AS sum2
FROM tblStockMaster LEFT JOIN tblReceivingD ON tblReceivingD.StockControlR=tblStockMaster.SmName
GROUP BY tblStockMaster.SmName
and the result is this
Stock Master sum2
ACTIVE MAPE 1 0
DEVELOPMENTAL READING 2 0
EDUCATIONAL TECHNOLOGY 2 0
FACILITATING LEARNING 2 0
GRAMAMAR AND COMPOSITION 396
HUMANITIES AND DIGITAL ARTS 0
INTRODUCTIORY MEDICAL PHYSICS FOR HEALTH 200
PHILLIPINE HISTORY AND GOVERMENT WORKBOOK 6th EDIT0
and for sum1
SELECT tblStockMaster.SmName AS StockMaster, SUM(isnull(tblPurchaseOrderD.TotalQuantity,0)) AS sum1
FROM tblStockMaster LEFT JOIN tblPurchaseOrderD ON tblPurchaseOrderD.StockControl=tblStockMaster.SmName GROUP BY tblStockMaster.SmName
and result is this
StockMaster sum1
ACTIVE MAPE 1 0
DEVELOPMENTAL READING 2 2
EDUCATIONAL TECHNOLOGY 2 3
FACILITATING LEARNING 2 1
GRAMAMAR AND COMPOSITION 3
HUMANITIES AND DIGITAL ARTS 0
INTRODUCTIORY MEDICAL PHYSICS FOR HEALTH 1
PHILLIPINE HISTORY AND GOVERMENT WORKBOOK 6th EDIT0
and all i want the result is like this
StockMaster sum1 sum2 Tqty
ACTIVE MAPE 1 0 0 0
DEVELOPMENTAL READING 2 2 0 -2
EDUCATIONAL TECHNOLOGY 2 3 0 -3
FACILITATING LEARNING 2 1 0 -1
GRAMAMAR AND COMPOSITION 3 396 393
HUMANITIES AND DIGITAL ARTS 0 0 0
INTRODUCTIORY MEDICAL PHYSICS FOR HEALTH 1 200 199
PHILLIPINE HISTORY AND GOVERMENT WORKBOOK 6th EDIT0 0 0
HOPE YOU help me this one if i post it wrong
please correct me sir! thanks for your kind
March 6, 2011 at 5:29 am
Table DDL and sample data in a ready to use format are still missing. Please reread the article I pointed you at.
As a side note: there are seveal people around not not able to unpack a RAR file. Therefore, I recommend to post the related data directly in your reply, wrapped by sql tags.
March 6, 2011 at 7:55 am
thank you sir here's my new attachment the whole mdf file
in the stored procedure you can see the ledger can you modife it if you want to! thank you for your reply again sir!
March 6, 2011 at 9:02 am
israel17_popeye (3/6/2011)
thank you sir here's my new attachment the whole mdf filein the stored procedure you can see the ledger can you modife it if you want to! thank you for your reply again sir!
???
Did you actually take the time to read the article I pointed you at?
I might not have been clear enough where to find it, so please go to this link[/url], read it and provide the data as described.
I also recommend to remove the DB you've attached inyour previous post unless it does not contain any real data. (I didn't bother to look at it though).
March 6, 2011 at 9:22 am
Hello,
here we go ,use the below query to get your result .
select a.StockMaster,sum1,sum2,(sum2-sum1) as TQty from
(SELECT tblStockMaster.SmName AS StockMaster, SUM(isnull(tblReceivingD.TotalQuantity,0)) AS sum2
FROM tblStockMaster LEFT JOIN tblReceivingD ON tblReceivingD.StockControlR=tblStockMaster.SmName
GROUP BY tblStockMaster.SmName) a ,
(SELECT tblStockMaster.SmName AS StockMaster, SUM(isnull(tblPurchaseOrderD.TotalQuantity,0)) AS sum1
FROM tblStockMaster LEFT JOIN tblPurchaseOrderD ON tblPurchaseOrderD.StockControl=tblStockMaster.SmName GROUP BY tblStockMaster.SmName) b
where a.StockMaster =b.StockMaster
Hope this helps you and let me know incase you need more help
Regards,
Subbu
Click here to Get Speedy answer or solution
March 6, 2011 at 9:27 am
i forgot to mentioned in my previous reply ,please follow the link given by Lutz and please do not share your data like you did.
Regards,
Subbu
Click here to Get Speedy answer or solution
March 6, 2011 at 10:38 am
ok sir thank you for your reply i think i got it
WITH PO
AS (SELECT SUM(tblPurchaseOrderD.TotalQuantity) AS sum1,
StockControl AS StockName
FROM tblPurchaseOrderD
GROUP BY StockControl),
R
AS (SELECT SUM(tblReceivingD.TotalQuantity) AS sum2,
StockControlR AS StockName
FROM tblReceivingD
GROUP BY StockControlR)
SELECT tblStockMaster.SmName AS StockMaster,
ISNULL(MAX(sum1), 0) AS sum1,
ISNULL(MAX(sum2), 0) As sum2,
ISNULL(MAX(sum2), 0) - ISNULL(MAX(sum1), 0) as tqty
FROM tblStockMaster
LEFT JOIN PO
ON PO.StockName = tblStockMaster.SmName
LEFT JOIN R
ON R.StockName = tblStockMaster.SmName
GROUP BY tblStockMaster.SmName order by SmName
some one told me to use this and i follow thanks for help sorry if i got wrong post i got confuse sorry sir hope u understand me
March 8, 2011 at 5:12 am
The solution you used here is temporary result set,CTE. You could as well have achieved this using a simple query like:
select <your col1>, a.sum as sum1, b.sum as sum2,(a.sum-b.sum) as Totalqty
from (Your query in CTE for sum1)a, (Your query in CTE for sum2)b
where a.YourJoinColumn = b.YourJoinColumn
If you read the replies, the exact answer to your question has also been posted by subbu_e. Just so you know, there are more than on ways to achieve it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply