Given the 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 is a self referential relationship back to products.
I'm basically trying to do a count of subcategories join products on
products.subcategory_id = subcategories.id
but limiting the count to 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 I did a simple join with products i'd get products (1, 2). I only want the number of distinct parent objects (stack_id is null) so 1 counts and 2 references 1 which was already counted so does not increase the count.
Subcategory id 2
Join would be (3, 5, 6, 7, 8). 3's stack_id is 1 so it counts 1. products 5, 6, and 7 reference 5 so that counts 1. product 8 counts 1.
Subcategory 3
Join is (4, 9, 10). 4 references 1, and 9 and 10 both reference 8.
Update
Removed extra possibly confusing columns, added sample data and output