MySQL 8.0

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

create table doorStatus
(Door varchar(100),	
  Block	varchar(100),
  key_Number varchar(100),	
  Broken varchar(100));
insert into doorStatus values ('door1','A','001','Y');
insert into doorStatus values ('door2','A','001','Y');
insert into doorStatus values ('door3','A','002','Y');
insert into doorStatus values ('door4','B','013','N');
SELECT block, key_number, 
COUNT(CASE WHEN broken = 'Y' THEN 1 END) AS Count_Broken
FROM doorStatus
GROUP BY block, key_number;


block
key_number
Count_Broken


A
001
2

A
002
1

B
013
0