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 |