I've got two tables, stock and item.


The stock table has a "level" field, for a desired inventory level, for example we always want to keep 10 widgets on hand so the stock record would be something like:

Stock_no Description Level
WID001 Widget, Octagonal 10


Now, in the item table, I have the actual items:

Stock_no Status
WID001 Shelf
WID001 Shelf
WID001 Shelf
...

Can I get a count of the items as well as a the stock number and description in a single SQL statment?

I tried

 SELECT stock.stock_no, equip_name, count(item.*);
   FROM stock, item;
   WHERE item.stock_no=stock.stock_no

but that just returns a syntax error.

Seems I should be grouping or something..