• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Select Items from Inventory, Count the items

I need to select the items from the table inventory (items) and their sub catagory (sub) and description (descr) then count the number of items in each subcategory group them by subcategory and then order them by the number of items in each sub category.  Output needs to include item, sub, descr and number of items.

Thanks in advance.
0
sfink01
Asked:
sfink01
1 Solution
 
hongjunCommented:
try this

select items, sub, description, count(sub) 'NumItem'
from inventory
group by items, sub, description
0
 
NightmanCTOCommented:
SELECT item, sub, description, COUNT(*) as itemCount
FROM inventory
GROUP BY item, sub, description
ORDER BY item, COUNT(*), sub
0
 
Brian CroweDatabase AdministratorCommented:
I don't believe the solution provided above will work since you are including the item in your grouping.

SELECT inventory.item, inventory.descr, category.sub, category.subcount
FROM inventory
INNER JOIN (SELECT sub, COUNT(*) AS SubCount FROM inventory GROUP BY sub) AS category
   ON inventory.sub = category.sub
ORDER BY category.subcount
0
 
NightmanCTOCommented:
It will work if the requirement is to count on the item,subcategory combination, which was my understanding.

If you need to show totals for the subcategory across ALL items, the BriCrowes solution would be better.
0
 
sfink01Author Commented:
This is what I ended up with from BriCrowe's answer I modified it but it pointed me in the right direction.

SELECT category.sub AS "Sub Category", inventory.item AS "Item No.",
 inventory.descr AS " Item Description",
 category.subcount as "No. Items in Sub Category"
FROM inventory
INNER JOIN (SELECT sub, COUNT(*) AS SubCount FROM inventory
GROUP BY sub) AS category ON inventory.sub = category.sub
ORDER BY category.subcount DESC

Thanks for the quick responses!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now