Counting and Joining in SQL (oracle)

My database contains 2 tables called products and product_categories.

Product_categories contain category_id and category_name Products contain category_id and other irrelevant info for this question.

I need to count the number of products using category_id from the products table and also display their name which is in the product_categories table.

Select * FROM product_categories

displays :

category_id, category_name
    1   CPU
    2   Video Card
    3   RAM
    4   Mother Board
    5   Storage

And

Select * FROM products

displays (condensed):

                category_id
399.77  564.89  1
481.56  554.99  1
4058.99 5499.99 2
3619.14 4139    2
2505.04 3254.99 2
... UPTO CATEGORY_ID 5

Current statement :

SELECT category_id , COUNT (1) AS "TOTAL"
FROM products
GROUP BY category_id;

Output:

Category_id, total
    1   70
    2   50
    5   108
    4   60

DESIRED RESULT: I need to display the category id followed by category name and finally the total number of products.

Answer

Join those tables:

select p.category_id,
       c.category_name,
       count(*) as total
from products p join product_categories c on c.category_id = p.category_id
group by p.category_id,
         c.category_name

You might want to turn it to outer join if you want to display categories that don’t exist in the products table.

Advertisement

Answer