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.
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.