I need help with a query.

[BobLoblaws]

Let's say there are 4 tables. Customer, Product, Invoice, and Returns.

Here is my SQL query so far. It works fine.

SELECT
	Customers.CustomerID,
	Customers.Long_desc AS 'Description',
	Products.ProductID,
	Products.p_Desc AS 'Description',
	SUM (Invoice.qty_ordered - Returns.qty_returned) AS 'QTY Sold',
FROM
	Customers, Products, Invoice, Returns
... and so on

However, I want to do a SUM on Returns.qty_returned where the REASON = 1 and 2.

But I can't put this in the WHERE clause b/c it will exclude the other records. I think the query should work like this.

SELECT
	Customers.CustomerID,
	Customers.Long_desc AS 'Description',
	Products.ProductID,
	Products.p_Desc AS 'Description',
	SUM (Invc.ordered - Returns.returned) AS 'QTY Sold',
        SUM (Returns.returned) WHERE REASON =1 AS 'Bent',
        SUM (Returns.returned) WHERE REASON =2 AS 'Cut'
FROM
	Customers, Products, Invc, Returns
... and so on

but it doesn't work...
Any ideas

 

[bperry]

Try:

SUM (Invc.ordered - Returns.returned) AS 'QTY Sold',
SUM (CASE when REASON = 1 or REASON = 2
then Returns.returned ELSE o END) as TheReturns
FROM....

 

[BeckahC]

Look in BOL under sub queries or sub query, I think that's what you need, but I don't recall the syntax.

Sorry I couldn't help more. BeckahC

 

[bperry]

Ooops I think I had an oh instead of a zero.Sorry.
Here it is with that minor correction.

SUM (Invc.ordered - Returns.returned) AS 'QTY Sold',
SUM (CASE when REASON = 1 or REASON = 2
then Returns.returned ELSE 0 END) as TheReturns
FROM....

 

[SQLBill]

I think this is what you are looking for (using bperry's suggestion and the titles (bent and cut) that you want to use.

SUM (Invc.ordered - Returns.returned) AS 'QTY Sold',
SUM (CASE when REASON = 1 then Returns.returned as 'Bent'
when REASON = 2 then Returns.returned as 'Cut'
ELSE 0 END)
FROM....

-SQLBill

 

[bperry]

Yes, SqlBill, I see that I missed that (didn't read closely enough). However I think this modification may be required:

SUM (Invc.ordered - Returns.returned) AS 'QTY Sold',
SUM (CASE when REASON = 1 then Returns.returned ELSE 0 END) as Bent,
SUM (CASE when REASON = 2 then Returns.returned ELSE 0 END) as Cut

FROM....


thanks for finding my error,
bp

 

[SQLBill]

bperry,

That's what we are all here for....to help each other out.

-SQLBill

 

[BobLoblaws]

Here is what I got to work.

'Leaker' = (SELECT
SUM(Returns.RET_QTY)
FROM Returns
WHERE Reason = 2 AND
Customer = Customers.Customer AND
Invc_Date BETWEEN '2002-04-10' AND '2002-04-13' AND
Product = Products.Product ),

Thanks for all the help.