SELECT SUM(QTYRECD) and Filter <> 0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT SUM(QTYRECD) and Filter <> 0

Author

Topic

new_bees
Starting Member

27 Posts

Posted - 2008-05-07 : 12:45:08

Hi Guys,

I'm facing another problem that I cannot solve. I hope SQL experts on this board will be able to help. I'm still learning this new T-SQL & SP thing.

Here are the data structure.

Table name:
This table holds the item description
IV00101
ITEMNMBR | ITEMDESC
ASCORBIC220 | Ascorbic Acid
AMINO1 | Amino Acid
CRTN1 | Creatine Malate

This table holds the Quantity Received, Quantity Sold and Date Receieved. Weight unit is Kilogram. LTN is the lot number
IV00300
ITEMNMBR|QTYRECVD|QTYSOLD|DATERECD | LTN
ASCORBIC220 | 5000 | 500 | 4/1/2008 | A569
ASCORBIC220 | 5000 | 0 | 4/1/2008 | A571
AMINO1 | 2000 | 300 | 4/6/2008 | A550
AMINO1 | 1000 | 0 | 4/8/2008 | A556
CRTN1 | 600 | 100 | 4/9/2008 | A551

What I want to do is to SUM up the quantity received in a month's view where Sold is <> 0.Unfortunately, I cannot use the where clause to filter out sold <> 0.

============================================

Here are my 1st SQL query.
This one will SUM up the quantity received but it shows product with zero sold.

SELECT DISTINCT a.ITEMDESC AS Description, SUM(b.QTY_Received) AS Received, SUM(b.QTY_Sold) AS Sold
FROM IV00101 a LEFT OUTER JOIN
IV_Lot_MSTR b ON a.ITEMNMBR = b.Item_Number
WHERE DATEPART([year], b.Date_Received) = '2008'
AND DATEPART([month], b.Date_Received) = '4'
GROUP BY a.ITEMDESC
ORDER BY a.ITEMDESC

1st SQL query result. It show the product with zero sold.
ITEMNMBR|QTYRECVD|QTYSOLD
ASCORBIC220 | 10000 | 500
OTHR1 | 500 | 0
OTHR2 | 300 | 0
AMINO1 | 3000 | 300
CRTN1 | 600 | 100

==========================================================

My 2nd SQL query:
This 2nd one I filtered it out the product with 0 sold. The problems is, it won't SUM up the quantity received.
SELECT DISTINCT a.ITEMDESC AS Description, SUM(b.QTY_Received) AS Received, SUM(b.QTY_Sold) AS Sold
FROM IV00101 a LEFT OUTER JOIN
IV_Lot_MSTR b ON a.ITEMNMBR = b.Item_Number
WHERE DATEPART([year], b.Date_Received) = '2008'
AND DATEPART([month], b.Date_Received) = '4'
AND b.QTY_Sold <> 0
GROUP BY a.ITEMDESC
ORDER BY a.ITEMDESC

2nd SQL query result. It's no longer showing product with zero sold, but it doesn't SUM up the quantity. It only SUMMING up the one that was sold.
ITEMNMBR|QTYRECVD|QTYSOLD
ASCORBIC220 | 5000 | 500
OTHR1 | 500 | 0
OTHR2 | 300 | 0
AMINO1 | 2000 | 300
CRTN1 | 600 | 100

===========================================

The final result I wanted should look like this below. It SUM up the quantity received and filter out sold = 0
ITEMNMBR|QTYRECVD|QTYSOLD
ASCORBIC220 | 10000 | 500
AMINO1 | 3000 | 300
CRTN1 | 600 | 100

Please help.

Dexter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 12:57:03

How about this:-

SELECT DISTINCT a.ITEMDESC AS Description, SUM(b.QTY_Received) AS Received, SUM(b.QTY_Sold) AS Sold
FROM IV00101 a LEFT OUTER JOIN
IV_Lot_MSTR b ON a.ITEMNMBR = b.Item_Number
WHERE DATEPART([year], b.Date_Received) = '2008' 
AND DATEPART([month], b.Date_Received) = '4'
GROUP BY a.ITEMDESC
HAVING SUM(b.QTY_Sold) >0
ORDER BY a.ITEMDESC

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-07 : 13:32:14

I believe distinct is unnecessary when using a group by clause.

An infinite universe is the ultimate cartesian product.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 13:40:02

quote:
Originally posted by cat_jesus

I believe distinct is unnecessary when using a group by clause.

An infinite universe is the ultimate cartesian product.

Thats true cat_jesus. thats not required.nice catch