ProductId Search

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » ProductId Search

ProductId Search

Rate Topic

Display Mode

Topic Options

Author

Message

2Tall

Posted Friday, April 18, 2008 2:18 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

I would like my query to return a unique value for the ProductId + count stock so I get a single value for available stock.

Query:

SELECT DISTINCT ProductId,ProductDescription,Quantity,QuantityOutstanding,StandardPrice
FROM Products 
INNER JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product
WHERE QuantityOutstanding >0

Example Output:
ProductId ProductDescription Quantity QuantityOutstanding
0-0125223214 MY PRODUCT 1 1
0-0125223214 MY PRODUCT 3 3
0-0125223214 MY PRODUCT 4 4
0-0125223214 MY PRODUCT 10 10

Can anyone assist?
Many Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #486961

Mahesh Bote

Posted Friday, April 18, 2008 3:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

If I am not wrong, do you want something like this ...?

SELECT 	ProductId
		,ProductDescription
		,Sum(Quantity)
		,Sum(QuantityOutstanding)
		,StandardPrice
FROM 		Products INNER JOIN dbo.Inventory 
		ON dbo.Inventory.Product = dbo.Products.Product
WHERE 		QuantityOutstanding >0
GROUP BY	ProductId
		,ProductDescription
		,StandardPrice

I think you must not in need of stock count but Total stock. Can you spread some more light on this with desired o/p so that we can give you better solution.

Mahesh

MH-09-AM-8694

Post #486985

2Tall

Posted Friday, April 18, 2008 3:15 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:43 AM
Points: 619, Visits: 1,065

Mahesh thanks for your time, I managed to resolve (well it looks OK). My query looks as follows:

SELECT DISTINCT ProductId,ProductDescription,ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0')AS Quantity,StandardPrice

FROM Products 

LEFT JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product

GROUP BY Products.ProductId, Products.ProductDescription, Products.StandardPrice

The Products table contains 1000's of records, the Inventory table less than a thousand so the query returned a load of NULL values. The above substitutes NULL with 0.

If anyone can see a potential problem with this approach then please advise.
Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Post #486992

« Prev Topic | Next Topic »

Permissions