Query using a counter using a foreign key

Question

The request I remove data from three tables: company, classes_by_companyand person. I have a foreign key in all tables with a name company_id. I use a left join to join tables with a suitable one company_id. I am trying to figure out the number of classes for a company and employees. I get values ​​that are not suitable for both. Here is the SQIDDLE

SELECT a.id, 
a.company_id,
a.status,
COUNT(c.company_id) AS classes_per_company,
COUNT(p.employee_id) AS employees_per_company
FROM company a
LEFT JOIN classes_by_company c
ON a.company_id = c.company_id
LEFT JOIN person p
ON a.company_id = p.company_id
GROUP BY a.company_id

Table structure:

CREATE TABLE company
    (
     id int auto_increment primary key,
     company_id int,
     status varchar(20)
    );
CREATE TABLE classes_by_company
(
 id int auto_increment primary key,
 company_id int,
 class_name varchar(20)
);
CREATE TABLE person
(
 id int auto_increment primary key,
employee_id int,
 company_id int,
 person_name varchar(20)
);

Answer 1

- company company_id, id? ? , , , 2 2 , 4 - 1 ( A + A, A + B, B + A, B + B). , , , :

SELECT a.id, a.company_id, a.status,
       c.count AS classes_per_company,
       p.count AS employees_per_company
FROM company a
LEFT JOIN (SELECT company_id, COUNT(*) as count
           FROM classes_by_company
           GROUP BY company_id) c
       ON a.company_id = c.company_id
LEFT JOIN (SELECT company_id, COUNT(*) as count
           FROM person
           GROUP BY company_id) p
       ON a.company_id = p.company_id

( - !)

, , , , , , .

Answer 2

, COUNT(DISTINCT c.company_id), company_id

SELECT a.id, 
       a.company_id,
       a.status,
       COUNT(DISTINCT c.id) AS classes_per_company,
       COUNT(DISTINCT p.employee_id) AS employees_per_company
FROM company a
LEFT JOIN classes_by_company c ON a.company_id = c.company_id
LEFT JOIN person p ON a.company_id = p.company_id
GROUP BY a.id,a.company_id,a.status

: COUNT(DISTINCT p.employee_id)

, COUNT(DISTINCT c.id) COUNT(c.company_id), , .