April 18, 2008 at 2:18 am
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:
ProductIdProductDescriptionQuantityQuantityOutstanding
0-0125223214MY PRODUCT11
0-0125223214MY PRODUCT33
0-0125223214MY PRODUCT44
0-0125223214MY PRODUCT1010
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
April 18, 2008 at 3:01 am
[font="Verdana"]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 BYProductId
,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
[/font]
MH-09-AM-8694
April 18, 2008 at 3:15 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply