Postgres 10

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 journey (
    client_id text,
    id text,
    city_id int,
    dolars int,
    status text
);

INSERT INTO journey VALUES
('c1', 'j1', 1, 10, 'completed'),
('c2', 'j2', 2, 11, 'completed'),
('c1', 'j3', 1, 12, 'failed'),
('c1', 'j4', 1, 13,'failed'),
('c2', 'j5', 2, 20, 'failed'),
('c3', 'j6', 4, 21,'failed'),
('c3', 'j7', 4, 22, 'failed'),
('c3', 'j8', 4, 23, 'failed'),
('c3', 'j9', 4, 26, 'failed'),
('c4', 'j10', 5, 27, 'completed'),
('c6', 'j11', 2, 10, 'completed'),
('c5', 'j12', 6, 12, 'failed'),
('c1', 'j13', 1, 15, 'failed'),
('c2', 'j14', 2, 43, 'failed'),
('c8', 'j15', 8, 41, 'failed'),
('c8', 'j16', 8, 32, 'failed'),
('c8', 'j17', 8, 48, 'failed'),
('c7', 'j18', 3, 10, 'failed');
18 rows affected

SELECT 
client_id
,COUNT(distinct CASE WHEN status = 'failed' THEN id END) AS count_FAILS
,COUNT(distinct CASE WHEN status = 'completed' THEN id END) AS count_COMPLETED
,COUNT(distinct CASE WHEN status IN ('failed') THEN id END)/NULLIF(CAST(COUNT(CASE WHEN status = 'completed' THEN id END) AS FLOAT),0) 
  
FROM journey
GROUP BY 1


client_id
count_fails
count_completed
?column?


c1
3
1
3

c2
2
1
2

c3
4
0
null

c4
0
1
0

c5
1
0
null

c6
0
1
0

c7
1
0
null

c8
3
0
null


SELECT 
client_id
,COUNT(distinct id) FILTER(WHERE status = 'failed') AS count_FAILS
,COUNT(distinct id) FILTER(WHERE status = 'completed') AS count_COMPLETED
,COUNT(distinct id) FILTER(WHERE status = 'failed')
 /NULLIF(1.0 * COUNT(distinct id) FILTER(WHERE status = 'completed'),0)
FROM journey
GROUP BY client_id;


client_id
count_fails
count_completed
?column?


c1
3
1
3.0000000000000000

c2
2
1
2.0000000000000000

c3
4
0
null

c4
0
1
0.00000000000000000000

c5
1
0
null

c6
0
1
0.00000000000000000000

c7
1
0
null

c8
3
0
null

client_id	count_fails	count_completed	?column?
c1	3	1	3
c2	2	1	2
c3	4	0	null
c4	0	1	0
c5	1	0	null
c6	0	1	0
c7	1	0	null
c8	3	0	null

client_id	count_fails	count_completed	?column?
c1	3	1	3.0000000000000000
c2	2	1	2.0000000000000000
c3	4	0	null
c4	0	1	0.00000000000000000000
c5	1	0	null
c6	0	1	0.00000000000000000000
c7	1	0	null
c8	3	0	null