Simple inventory SQL query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Simple inventory SQL query

Simple entry level query question for anyone willing to help. I have a table that contains each of the items (parts) in my inventory and each of them has a different status: In Stock, Shipped, RMAd, etc. I would like to run a query that will tell me how many of each type of part I have with a Status of "In Stock". My "parts" table looks as follows: ID Product Type Status
01 Motherboard In Stock
02 CPU In Stock
03 Hard drive Shipped
04 Hard drive In Stock
05 CPU In Stock
06 RAM Shipped Again, my query’s result should tell me how many of each product type I have In Stock… in other words, I would want my results to look something like this: Product Type Available
Motherboard 1
CPU 2
Hard Drive 1
RAM 0 The syntax for the query to give me these results would be greatly appreciated, thanks! xdeanda
SELECT [Product Type], COUNT(*) As Available
FROM Parts
WHRE Status = ‘In Stock’
GROUP BY [Product Type]
Roji. P. Thomas
http://toponewithties.blogspot.com

This is great, thank you. Unfortunately when I run the suggested query I am being asked what "Status" I want before I run the query. Also, I am not getting the product type if there are 0 parts in stock, which makes sense but doesn’t help me. I would like to launch the query and be able to tell that there is one Motherboard in stock, two CPUs, one hard drive and zero sticks of RAM. As I add more product types, it will be important to know that I have zero parts for whatever items I am out of stock of, make sense? Any other suggestions? Thanks again for your help. xdeanda

SELECT [Product Type], sum(case ([Product Type])
when ‘In Stock’ then 1
else 0
end) As Available
FROM Parts p
WHeRE Status = ‘In Stock’
GROUP BY [Product Type] Or if you have ProductTypes reference table and [Product Type] can’t be null:
SELECT pt.ProductType, COUNT(p.[product type]) As Available
FROM ProductTypes pt
left join Parts p on pt.productType = p.[Product Type] and Status = ‘In Stock’
GROUP BY pt.productType
What do you mean with "being asked"? What is your front-end? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
I just meant that a prompt dialog comes up. I am testing the query directly in Access. xdeanda
]]>