Aggregate Function - help with condition

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 2005 Forums

Transact-SQL (2005)

Aggregate Function - help with condition

Author

Topic

Maxx
Starting Member

2 Posts

Posted - 2008-11-27 : 13:04:56

Hey all,

I am a bloody beginner. I use Sun's Open Office DB to track Inventory and Sales for us. I have an aggregate function to display what's on Stock which works ok. However, if I have an item in the warehouse but no sales yet "On Stock" returns no value.

Here is my question, if there are no sales can I return a different variable, e.g. Q purchased.

SELECT "Warehouse"."Product_ID", "Warehouse"."Description", "Warehouse"."Quantity_Purchased", ( "Warehouse"."Quantity_Purchased" - "Total Units Sold"."Total_Sold" ) AS "On_Stock"
FROM "Warehouse"
LEFT OUTER JOIN "Total Units Sold" ON "Warehouse"."Product_ID" = "Total Units Sold"."Product_ID"

Thanks,
Max

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 13:08:03

do you mean this?


SELECT "Warehouse"."Product_ID", "Warehouse"."Description", "Warehouse"."Quantity_Purchased", COALESCE( "Warehouse"."Quantity_Purchased" - "Total Units Sold"."Total_Sold" ,Qpurchased) AS "On_Stock"
FROM "Warehouse"
LEFT OUTER JOIN "Total Units Sold" ON "Warehouse"."Product_ID" = "Total Units Sold"."Product_ID"