List and Count items with a JOIN with SQL

I’m trying to create a basic rapport from these 2 tables:

Table Products

|--------|----------------|----------|
| PRO_Id | PRO_CategoryId | PRO_Name |
|--------|----------------|----------|
|      1 |             98 | Banana   |
|      2 |             98 | Apple    |
|--------|----------------|----------|

Table Categories

|--------|----------|
| CAT_Id | CAT_Name |
|--------|----------|
|     98 | Fruits   |
|     99 | Other    |
|--------|----------|

What I needed is this output:

|------------|
| Categories |
|------------|
| Fruits (2) |
|------------|

I would like a report listing all the categories from Categories but only when product from Products has a link (with is the case form Fruits but not for Other).

This is where I am actually:

SELECT CAT_Name, COUNT(PRO_Name IN sum)
FROM Categories
JOIN Products
ON Products.PRO_CategoryId = Categories.CAT_Id as sum
ORDER BY CAT_Name ASC

Anyone to help me with this please ?

Thanks.

Answer

You are pretty close. You need to get rid of the garbage in the query and use a group by:

SELECT c.cat_name, COUNT(*)
FROM Categories c JOIN
     Products p
     ON p.PRO_CategoryId = c.CAT_Id
GROUP BY c.CAT_Name ;

Notes:

• SELECT * is not appropriate for an aggregation query. What you want to select is.
• This puts the count in a separate column which seems to be your intention, despite the sample results.
• COUNT(pro_name in sum) doesn’t make sense.
• as sum doesn’t make sense.