sql - Postgres LEFT JOIN with SUM, missing records -

sql - Postgres LEFT JOIN with SUM, missing records -

i trying count of types of records in related table. using left join.

so have query isn't quite right , 1 returning right results. right results query has higher execution cost. id utilize first approach, if can right results. (see http://sqlfiddle.com/#!15/7c20b/5/2)

create table people( id serial, name varchar not null ); create table pets( id serial, name varchar not null, kind varchar not null, live boolean not null default false, person_id integer not null ); insert people(name) values ('chad'), ('buck'); --can't maintain pets live insert pets(name, alive, kind, person_id) values ('doggio', true, 'dog', 1), ('dog master flash', true, 'dog', 1), ('catio', true, 'cat', 1), ('lucky', false, 'cat', 2);

my goal table of people , counts of kinds of pets have alive:

| id | alive_dogs_count | alive_cats_count | |----|------------------|------------------| | 1 | 2 | 1 | | 2 | 0 | 0 |

i made illustration more trivial. in our production app (not pets) there 100,000 dead dogs , cats per person. pretty screwed know, illustration simpler relay ;) hoping filter 'dead' stuff out before count. have slower query in production (from sqlfiddle above), love left bring together version working.

typically fastest if fetch all or rows:

select pp.id , coalesce(pt.a_dog_ct, 0) alive_dogs_count , coalesce(pt.a_cat_ct, 0) alive_cats_count people pp left bring together ( select person_id , count(kind = 'dog' or null) a_dog_ct , count(kind = 'cat' or null) a_cat_ct pets live grouping 1 ) pt on pt.person_id = pp.id;

indexes irrelevant here, total table scans fastest. except if live pets rare case, partial index should help. like:

create index pets_alive_idx on pets (person_id, kind) alive;

i included columns needed query (person_id, kind) allow index-only scans.

sql fiddle.

typically fastest small subset or single row:

select pp.id , count(kind = 'dog' or null) alive_dogs_count , count(kind = 'cat' or null) alive_cats_count people pp left bring together pets pt on pt.person_id = pp.id , pt.alive <some status retrieve little subset> grouping 1;

you should @ to the lowest degree have index on pets.person_id (or partial index above) - , perchance more, depending ion where condition.

related answers:

count on left bring together not returning 0 values group or distinct after bring together returns duplicates get count of foreign key multiple tables

sql postgresql left-join aggregate-functions