May I ask how to count the broken door in below table? I want to show the record of Block B also, but I only can show Block A record.

Also, if my selection table query is too long, but I will reuse many times. How can I define the valuable to the long selection query?

Table: doorStatus

Except result:

Thank you for your help.

CodePudding user response：

One way to do it: group by block and key_number, count the number of broken.

-- prep data
create table door_status (
    door        varchar(10),
    block       char(1),
    key_number  varchar(3),
    broken      char(1));
    
insert into door_status
values
('door1','A','001','Y'),
('door2','A','001','Y'),
('door3','A','002','Y'),
('door4','B','013','N');

-- query
select block,
       key_number,
       sum(case broken when 'Y' then 1 else 0 end) as count_broken
  from door_status
 group by 1,2;

Result:

block|key_number|count_broken|
----- ---------- ------------ 
A    |001       |           2|
A    |002       |           1|
B    |013       |           0|

CodePudding user response：

That's just a GROUP BY clause on both block and key_number with a CASE on broken's value. COUNT the "Y" entries only:

SELECT block, key_number, 
COUNT(CASE WHEN broken = 'Y' THEN 1 END) AS Count_Broken
FROM doorStatus
GROUP BY block, key_number;

Will produce this outcome based on your sample data:

See here: db<>fiddle