By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table mytable (
id varchar(10),
rowNum int,
status varchar(10)
);
insert into mytable values
('aa23' ,1 ,'HIT'),
('aa23' ,1 ,'HIT'),
('aa23' ,1 ,'HIT'),
('aa23' ,2 ,'MISS'),
('aa23' ,2 ,'MISS'),
('aa24' ,2 ,'MISS'),
('aa24' ,2 ,'MISS');
CREATE TABLE
INSERT 0 7
WITH CTE AS
( SELECT DISTINCT id,rowNum,status
FROM mytable)
SELECT id, status, COUNT(*) as count_
FROM CTE
GROUP BY id, status
| id | status | count_ |
|---|---|---|
| aa24 | MISS | 1 |
| aa23 | HIT | 1 |
| aa23 | MISS | 1 |
SELECT 3