Tudor Andrei Tudor Andrei - 10 months ago 76
SQL Question

SQL number of products

I have two tabels :

idProduct PK
Category FK

idCategory PK
Category Name

if i have 20 products of different categories for example 3 cars 5 planes 9 types of food
how do i get all of them in a new joined table like this

Category Name (from --Category--) Amount of every product (from --Products--)
Cars 3
Planes 9

And so on , i don't have a table with all the elements from each category so what should i do ?

Answer Source

You may looking for simple join with aggregate function

SELECT CategoryName,COUNT(p.Category ) AS [Amount of every product ]
FROM Category c
INNER JOIN Product p ON c.idCategory = p.Category 
GROUP BY CategoryName