看起来像外部连接（这样你就可以显示没有任何产品的类别），计算产品（不是“一般”，因为你会得到错误的结果;我会表明我的意思）。
示例数据（您的数据错误;如果类别5不存在，豆腐就不能放在类别5中外键约束不允许）：

SQL> with
  2  categories (categoryid, categoryname) as
  3    (select 1, 'beverages'   from dual union all
  4     select 2, 'condiments'  from dual union all
  5     select 3, 'confections' from dual union all
  6     select 5, 'category 5'  from dual
  7    ),
  8  products (productid, productname, categoryid) as
  9    (select 1, 'chai' , 1 from dual union all
 10     select 2, 'chang', 1 from dual union all
 11     select 3, 'tofu' , 5 from dual
 12    )

查询：计数产品（第14行）：

13  select c.categoryname,
 14         count(p.productid) number_of_products
 15  from categories c left join products p on p.categoryid = c.categoryid
 16  group by c.categoryname;

CATEGORYNAME         NUMBER_OF_PRODUCTS
-------------------- ------------------
beverages                             2
category 5                            1
condiments                            0
confections                           0

SQL>

如果你使用count(*)（第14行），你会得到错误的结果，因为你会计算类别本身：

13  select c.categoryname,
 14         count(*) number_of_products
 15  from categories c left join products p on p.categoryid = c.categoryid
 16  group by c.categoryname;

CATEGORYNAME         NUMBER_OF_PRODUCTS
-------------------- ------------------
beverages                             2
category 5                            1
condiments                            1     --> no products in
confections                           1     --> these two categories!

SQL>

但是，如果你对没有任何产品的类别不感兴趣，inner join和count(*)（以及count(p.productid)）可以：

13  select c.categoryname,
 14         count(*) number_of_products
 15  from categories c join products p on p.categoryid = c.categoryid
 16  group by c.categoryname;

CATEGORYNAME         NUMBER_OF_PRODUCTS
-------------------- ------------------
beverages                             2
category 5                            1

SQL>

对于每个类别，我列出了所有的产品及其计数。让我知道这是否回答了你的问题。
如果有，请投赞成票并接受答案。如果没有，请添加更多细节并澄清问题。
以后如果您有问题，请像我下面所做的那样提供CREATE表语句。

CREATE TABLE categories(  
category_id, category_name) AS
SELECT 1, 'Beverages' FROM DUAL UNION ALL
SELECT 2, 'Condiments' FROM DUAL UNION ALL
SELECT 3, 'Confections' FROM DUAL;

CREATE TABLE products(  
product_id, product_name, category_id) AS
SELECT 1, 'Chai',1 FROM DUAL UNION ALL
SELECT 2, 'Chang',1 FROM DUAL UNION ALL
SELECT 3, 'Tofu', 2 FROM DUAL;

select  c.category_id,
        c.category_name,
        listagg(p.product_name,', ') within group(order by p.product_name) product_list,
        count(p.product_name) cnt
  from  categories c,
        products p
  where c.category_id = p.category_id(+)
  group by c.category_id,
           c.category_name
  order by c.category_id,
           c.category_name
/

CATEGORY_ID CATEGORY_NA PRODUCT_LIST                CNT
----------- ----------- -------------------- ----------
          1 Beverages   Chai, Chang                   2
          2 Condiments  Tofu                          1
          3 Confections                               0