How to count values in a column that come after a particular value-CodePudding

I have a column that has two types of values A or B. I need to find the count of values that come after the first occurrence of A. eg

column
B
B
B
A
B
A
B

The result in this case would be 4 as their are 4 entries after the first occurrence of A(including A)

CodePudding user response：

You can use a sub query to get the id number (of whatever column you are using to order the columns) of the first 'A'.

CREATE TABLE t (
    id serial,
    col char(1));
insert into t (col) values
('B'),
('B'),
('A'),
('B'),
('A'),
('B')

✓

6 rows affected

select 
  count(*) NUM
from t
where id >= 
(select MIN(id) 
from t 
where col = 'A');

| num |
| --: |
|   4 |

db<>fiddle here