m9ZcySeb

1drop table if exists a;
2create table a  (
3	id serial primary key,
4	name varchar(255) not null
5);
6
7drop table if exists b;
8create table b (
9	id serial primary key,
10	a_id integer references a(id),
11	clicks integer not null
12);
13
14drop table if exists c;
15create table c (
16	id serial primary key,
17	b_id integer references b(id),
18	name varchar(255),
19	value integer
20);
21
22insert into a (name) values ('foo'), ('bar'), ('baz');
23insert into b (a_id, clicks) values (1, 4), (2, 5), (3, 6), (3, 7);
24insert into c (b_id, name, value) values (1, 'abc', 4), (1, 'xyz', 1), (1, 'efg', 2), (2, 'abc', 3), (2, 'xyz', 2), (2, 'efg', 2), (3, 'abc', 6), (3, 'xyz', 3), (3, 'efg', 1);
25
26
27select a.id, a.name, sum(b.clicks) total_clicks
28from a
29inner join b on b.a_id = a.id
30-- This would mess up the sums of total_clicks, but I need it for select from c... is there any way to exclude the duplicates produced by joining c in the total_clicks?
31-- inner join c on c.b_id = b.id
32group by a.id, a.name
33
34select a.id, a.name, sum(c.value) filter(where c.name = 'abc') total_abc, sum(c.value) filter(where c.name = 'xyz') total_xyz, sum(c.value) filter(where c.name = 'efg') total_efg
35from a
36inner join b on b.a_id = a.id
37inner join c on c.b_id = b.id
38group by a.id, a.name;