/*
4.1.10-nt

table_1
+----+-------+
| id | name  |
+----+-------+
|  1 | alpha |
|  2 | beta  |
|  3 | gamma |
+----+-------+

table_2
+----+------+
| id | wert |
+----+------+
|  1 |   10 |
|  1 |   10 |
|  1 |   30 |
|  1 |   40 |
|  2 |   10 |
|  2 |   10 |
|  2 |   10 |
|  2 |   10 |
|  3 |   20 |
|  3 |   30 |
|  3 |   30 |
|  3 |   40 |
+----+------+
*/

CREATE 
#TEMPORARY 
TABLE table_1 (
    id INT, 
    name CHAR(5)
);

INSERT INTO table_1 
VALUES(1, "alpha"), 
    (2, "beta"), 
    (3, "gamma");

CREATE 
#TEMPORARY 
TABLE table_2 (
    id INT, 
    wert INT
);

INSERT INTO table_2 
VALUES(1,10),
    (1, 10),
    (1, 30),
    (1, 40),
    (2, 10),
    (2, 10),
    (2, 10),
    (2, 10),
    (3, 20),
    (3, 30),
    (3, 30),
    (3, 40);


SELECT 
    t1.id,
    t1.name,
    COUNT( t2_10.id ) AS count_10,
    COUNT( t2_20.id ) AS count_20,
    COUNT( t2_30.id ) AS count_30,
    COUNT( t2_40.id ) AS count_40
FROM table_1 t1
LEFT JOIN table_2 t2_10
    ON t2_10.id = t1.id
    AND t2_10.wert = 10
LEFT JOIN table_2 t2_20
    ON t2_20.id = t1.id
    AND t2_20.wert = 20
LEFT JOIN table_2 t2_30
    ON t2_30.id = t1.id
    AND t2_30.wert = 30
LEFT JOIN table_2 t2_40
    ON t2_40.id = t1.id
    AND t2_40.wert = 40

GROUP BY t1.id;

+----+-------+----------+----------+----------+----------+
| id | name  | count_10 | count_20 | count_30 | count_40 |
+----+-------+----------+----------+----------+----------+
|  1 | alpha |        2 |        0 |        1 |        1 |
|  2 | beta  |        4 |        0 |        0 |        0 |
|  3 | gamma |        0 |        1 |        2 |        1 |
+----+-------+----------+----------+----------+----------+

SELECT
    t1.id,
    t1.name,
    COUNT( t2_10.id ) AS count_10,
    COUNT( t2_20.id ) AS count_20,
    COUNT( t2_30_u_40.id ) AS count_30_u_40
FROM table_1 t1
LEFT JOIN table_2 t2_10
    ON t2_10.id = t1.id
    AND t2_10.wert = 10
LEFT JOIN table_2 t2_20
    ON t2_20.id = t1.id
    AND t2_20.wert = 20
LEFT JOIN table_2 t2_30_u_40
    ON t2_30_u_40.id = t1.id
    AND t2_30_u_40.wert IN (30, 40)] # <------

GROUP BY t1.id;

+----+-------+----------+----------+---------------+
| id | name  | count_10 | count_20 | count_30_u_40 |
+----+-------+----------+----------+---------------+
|  1 | alpha |        2 |        0 |             2 |
|  2 | beta  |        4 |        0 |             0 |
|  3 | gamma |        0 |        1 |             3 |
+----+-------+----------+----------+---------------+

SELECT
    t2.id,
    t1.name,
    SUM(CASE t2.wert WHEN 10 THEN 1 ELSE 0 END) count_10,
    SUM(CASE t2.wert WHEN 20 THEN 1 ELSE 0 END) count_20,
    SUM(CASE t2.wert WHEN 30 THEN 1 WHEN 40 THEN 1 ELSE 0 END) count_30_u_40
FROM table_1 t1, table_2 t2
WHERE t1.id = t2.id
GROUP BY t2.id