-1

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

Door Block key_Number Broken
door1 A 001 Y
door2 A 001 Y
door3 A 002 Y
door4 B 013 N

Except result:

Block key_number Count_Broken
A 001 2
A 002 1
B 013 0

Thank you for your help.

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
Matt Tin
  • 1
  • 1
  • Note: Removed the tags "oracle" and "mysql". If you use a specific DB, tag this DB type only. In this case, the syntax of the required query will very likely not differ between different DB's. – Jonas Metzler Nov 16 '22 at 05:33
  • Does this answer your question? [Column count based on a condition](https://stackoverflow.com/questions/33169707/column-count-based-on-a-condition) – d r Nov 16 '22 at 05:44

2 Answers2

2

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|
JHH
  • 1,331
  • 1
  • 2
  • 9
  • I'd recommend `GROUP BY block, key_number`, since it's standard SQL and portable. Especially since the dbms is unknown. – jarlh Nov 16 '22 at 07:50
  • I agree, using column names when grouping is generally recommended to keep queries as most clear as possible. Due to the same reason, I would prefer to use COUNT instead of SUM because the entries should be counted, not sumed. Of course, SUM will also work correctly in this case anyway. – Jonas Metzler Nov 16 '22 at 07:54
1

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:

Block key_number Count_Broken
A 001 2
A 002 1
B 013 0

See here: db<>fiddle

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17