id serial primary key

);

create table items (

id serial primary key,

type int

);

create table purchase_items (

id serial primary key,

purchase_id int,

item_id int,

price decimal,

sold int

);

create table purchase_discounts (

id serial primary key,

purchase_id int,

discount_amount int

);

insert into items (id, type) values (1, 5);

insert into purchase (id) values (200);

insert into purchase_discounts (id, purchase_id, discount_amount) values (1, 200, 10);

insert into purchase_discounts (id, purchase_id,discount_amount) values (2, 200, 2);

insert into purchase_items (id, purchase_id, item_id, price, sold) values (1234, 200, 1, 10, 1);

(

SELECT t.item_id, t.purchase_items_ids, t.total_sold, t.discount_amount FROM

(

SELECT purchase_items.item_id AS item_id,

ARRAY_AGG(purchase_items.id) AS purchase_items_ids,

SUM(purchase_items.sold) as total_sold,

SUM((select sum(discount_amount) from purchase_discounts

where purchase_discounts.purchase_id = purchase.id)) as discount_amount

FROM items

INNER JOIN purchase_items ON purchase_items.item_id = items.id

INNER JOIN purchase ON purchase.id = purchase_items.purchase_id

WHERE

purchase.id = 200

GROUP by

purchase_items.item_id

) as t

) AS p_values;