Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ProductId Search Expand / Collapse
Author
Message
Posted Friday, April 18, 2008 2:18 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Posted Friday, April 18, 2008 3:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 11:08 AM
Points: 1,091, Visits: 1,207
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
Posted Friday, April 18, 2008 3:15 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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 »

Add to briefcase

Permissions Expand / Collapse