MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE scu_banks
    (`id` int, `type` int, `name` varchar(5))
INSERT INTO scu_banks
    (`id`, `type`, `name`)
VALUES
    (1, 1, 'One'),
    (2, 1, 'Two'),
    (3, 2, 'Three'),
    (4, 3, 'Four')

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE scu_statement
    (`id` int, `code` int, `status` int)
INSERT INTO scu_statement
    (`id`, `code`, `status`)
VALUES
    (1, 1, 0),
    (2, 1, 1),
    (3, 2, 0),
    (4, 1, 0)

Records: 4  Duplicates: 0  Warnings: 0


SELECT b.type `scu_banks.type`,
b.name `scu_banks.name`,
COUNT(s.status) `status`,
b.id `scu_banks.id`
FROM scu_banks b
LEFT JOIN scu_statement s ON s.code = b.id AND s.status = 0
GROUP BY b.id, b.name, b.type


scu_banks.type
scu_banks.name
status
scu_banks.id


1
One
2
1

1
Two
1
2

2
Three
0
3

3
Four
0
4