postgresql - Postgres count with self referential join condition -

given following structure

create table products (     id integer not null,     subcategory_id integer,     stack_id integer, )  create table subcategories (   id integer not null,   name character varying(255) ) 

where products.stack_id self referential relationship products.

i'm trying count of subcategories join products on

products.subcategory_id = subcategories.id   

but limiting count once per distinct stack group.

sample subcategories table

id    name 1     subcategory_1 2     subcategory_2 3     subcategory_3 

sample products table

id    subcategory_id    stack_id     1     1                 null         2     1                 1            3     2                 1            4     3                 1            5     2                 null         6     2                 5            7     2                 5            8     2                 null         9     3                 8            10    3                 8  

sample desired output

id    name             total  1     subcategory_1    1     (row 1) 2     subcategory_2    3     (row 1 + row 5 + row 8) 3     subcategory_3    2     (row 1 + 8) 

explanation of output

subcategory id 1
if did simple join products i'd products (1, 2). want number of distinct parent objects (stack_id null) 1 counts , 2 references 1 counted not increase count.

subcategory id 2
join (3, 5, 6, 7, 8). 3's stack_id 1 counts 1. products 5, 6, , 7 reference 5 counts 1. product 8 counts 1.

subcategory 3
join (4, 9, 10). 4 references 1, , 9 , 10 both reference 8.

update

removed possibly confusing columns, added sample data , output

if maximum depth of references 1 level, simple query job:

select subcategory_id, name, count(*) (     select distinct subcategory_id, coalesce(stack_id, id) stack_id     products     ) sub join subcategories s on s.id = sub.subcategory_id group 1, 2 order 1, 2;   subcategory_id |     name      | count  ----------------+---------------+-------               1 | subcategory_1 |     1               2 | subcategory_2 |     3               3 | subcategory_3 |     2 (3 rows) 

this recursive query works on references deeper 1 level:

with recursive pr(id, subcategory_id, stack_id, stack) (     select id, subcategory_id, stack_id, array[id]     products union     select pr.id, pr.subcategory_id, products.stack_id, pr.stack_id || pr.stack     pr     join products on pr.stack_id = products.id     ) select distinct on (id) id, subcategory_id, stack pr order id, array_length(stack, 1) desc   id | subcategory_id | stack   ----+----------------+--------   1 |              1 | {1}   2 |              1 | {1,2}   3 |              2 | {1,3}   4 |              3 | {1,4}   5 |              2 | {5}   6 |              2 | {5,6}   7 |              2 | {5,7}   8 |              2 | {8}   9 |              3 | {8,9}  10 |              3 | {8,10} (10 rows) 

join subcategories above dataset:

select subcategory_id, name, count(*) (     select distinct subcategory_id, stack[1]     (         recursive pr(id, subcategory_id, stack_id, stack) (             select id, subcategory_id, stack_id, array[id]             products         union             select pr.id, pr.subcategory_id, products.stack_id, pr.stack_id || pr.stack             pr             join products on pr.stack_id = products.id             )         select distinct on (id) id, subcategory_id, stack         pr         order id, array_length(stack, 1) desc         ) sub     ) sub join subcategories s on s.id = sub.subcategory_id group 1, 2 order 1, 2   subcategory_id |     name      | count  ----------------+---------------+-------               1 | subcategory_1 |     1               2 | subcategory_2 |     3               3 | subcategory_3 |     2 (3 rows)