Oracle SQL Total Products By Category-CodePudding

I have an exercise that says that I have to do this query: Names of the product categories (CategoryName) and total number of products for each of the categories

I have two tables: -The first table is called "Categories" where the category of the products is found

-The second table is called "Products" and it contains the products

The primary key "Categoryid" of Categories is shared as a foreign with Products, so I think what to do is count how many products each id has and display the name on the left

I am going to leave two examples with the content of the two tables, since the two tables cannot be joined, but not how to count the number of products for each category

Table Categories:

| Categoryid | Categoryname ||
| --------  | ------------- ||  
| 1         | Beverages     ||  
| 2         | Condiments    ||
| 3         | Confections   ||

Table Products:

| Productid | Productname   | Categoryid ||
| --------  | ------------- | ---------- ||
| 1         | Chai          |  1         ||
| 2         | Chang         |  1         ||
| 3         | Tofu          |  5         ||

How it should come out:

| CategoryName | TotalProducts||
| --------     | -----------  ||  
| Beverages    | 10           ||  
| Condiments   | 5            ||  
| Confections  | 3            ||

I don't know how to count the number of products for each category

i try this:

SELECT Categoryname COUNT(*)
FROM Categories  JOIN  Products  ON Categories.Categoryid=Products.Categoryid;

CodePudding user response：

Looks like outer join (so that you would display categories that don't have any products), counting products (not "generally" because you'd get false result; I'll show what I mean).

Sample data (your data is wrong; you can't have Tofu in category 5 if that category doesn't exist; foreign key constraint wouldn't allow it):

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    )

Query: count products (line #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>

If you used count(*) (line #14), you'd get wrong result as you'd count category itself:

 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>

However, if you aren't interested in categories that don't have any products, inner join and count(*) (as well as count(p.productid)) would do:

 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>

CodePudding user response：

For each category I listed all the products and its count. Let me know if this answers your question.

If so, upvote and accept the answer. If not, please add more details to the question and clarify.

In the future if you ask questions please provide CREATE tables statements like I did below.


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