MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.20


CREATE TABLE category
(
  id INTEGER,
  name VARCHAR (25),
  deleted_at DATE
);
INSERT INTO category VALUES (1, 'Food', NULL), (2, 'Stationery', NULL);
SELECT * FROM category;


id
name
deleted_at


1
Food
null

2
Stationery
null


CREATE TABLE product
(
  id INTEGER,
  cate_id INTEGER,
  vendor_id INTEGER,
  name VARCHAR (25),
  deleted_at DATE
);
INSERT INTO product VALUES (1, 1, 1, 'Bread', NULL), (2, 1, 2, 'Milk', NULL), (5, 2, 2, 'Pencil', NULL);
SELECT * FROM product;


id
cate_id
vendor_id
name
deleted_at


1
1
1
Bread
null

2
1
2
Milk
null

5
2
2
Pencil
null


CREATE TABLE vendor
(
  id INTEGER,
  name VARCHAR (25),
  deleted_at DATE
);
INSERT INTO vendor VALUES (1, 'Woolies', NULL), (2, 'Cole', '2020-01-18');
SELECT * FROM vendor;


id
name
deleted_at


1
Woolies
null

2
Cole
2020-01-18


SELECT c.id, c.name, COUNT(1)
FROM category c
LEFT JOIN product p 
  ON c.id = p.cate_id AND c.deleted_at IS NOT NULL
JOIN vendor v ON p.vendor_id = v.id AND v.deleted_at IS NOT NULL
GROUP BY c.id, c.name;
SELECT 
  p.id AS product_id,
  p.name AS product_name,
  COUNT(p.id) AS product_count
FROM product p
JOIN category c ON p.cate_id = c.id AND c.deleted_at IS NULL
JOIN vendor v ON p.vendor_id = v.id AND v.deleted_at IS NULL
WHERE p.deleted_at IS NULL
GROUP BY p.id, p.name;


product_id
product_name
product_count


1
Bread
1


SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product,
    p.id AS product_id
FROM
    category c
LEFT JOIN product p 
    ON p.cate_id = c.id AND p.deleted_at IS NULL
INNER JOIN vendor v
    ON v.id = p.vendor_id AND v.deleted_at IS NULL
GROUP BY c.id;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_513414584.c.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product,
    p.id AS product_id
FROM
    category c
LEFT JOIN (SELECT p1.* FROM product p1 INNER JOIN vendor v ON v.id = p1.vendor_id AND v.deleted_at IS NULL) 
    AS p ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_513414584.c.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT DISTINCT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
    -- p.id AS product_id
FROM
    category c
LEFT JOIN product p 
    ON p.cate_id = c.id AND p.deleted_at IS NOT NULL
LEFT JOIN vendor v
    ON v.id = p.vendor_id AND v.deleted_at IS NOT NULL
GROUP BY c.id, c.name, p.id;


cate_id
cate_name
total_product


1
Food
0

2
Stationery
0


SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
    -- p.id AS product_id
FROM
    category c
LEFT JOIN (SELECT p1.* FROM product p1 INNER JOIN vendor v ON v.id = p1.vendor_id AND v.deleted_at IS NULL) 
    AS p ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name, p.id;


cate_id
cate_name
total_product


1
Food
1

2
Stationery
0


Category
--|----------|----------|
id|name      |deleted_at|
--|----------|----------|
 1|Food      |      NULL|
 2|Stationery|      NULL|

Product
--|-------|---------|------|----------|
id|cate_id|vendor_id|name  |deleted_at|
--|-------|---------|------|----------|
 1|      1|        1|Bread |      NULL|
 2|      1|        2|Milk  |      NULL|
 5|      2|        2|Pencil|      NULL|

Vendor
--|-------|----------|
id|name   |deleted_at|
--|-------|----------|
 1|Woolies|      NULL|
 2|Cole   |2020-01-18|
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Category
--|----------|----------|
id|name      |deleted_at|
--|----------|-----' at line 1