How to count the number of products in a category?

Question

SQL

Hello. Help me write a SQL query. I need to count the number of products in a category. As far as I understand, I need LEFT or RIGT JOIN, but I did not figure out how to connect it all and get data.
Menu table (they are also categories)

CREATE TABLE `main_menu` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `parent` int(11) NOT NULL,
  `alias` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Products table:

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `img` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `descr` text NOT NULL,
  `full_text` text NOT NULL,
  `category` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `code` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here, in the `category` int(11) NOT NULL column, the category id from the table above is written or stored ( `main_menu` `id` int(11) ).
I connected the columns

ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category`) REFERENCES `main_menu` (`id`);
COMMIT;

It seems that I linked it correctly (but I'm not sure), but I didn't succeed in compiling an sql query.

Answer 1

select c.id, c.title, count(*)
from products p
join main_menu c on c.id = p.category
group by c.id, c.title