Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table purchase (
    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);


CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT array_to_json(array_agg(p_values)) FROM 
( 
  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(purchase_discounts.discount_amount) 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
       LEFT JOIN purchase_discounts ON purchase_discounts.purchase_id = purchase.id
       WHERE purchase.id = 200
       GROUP by purchase_items.item_id
    ) as t 
  INNER JOIN items i ON i.id = t.item_id 
) AS p_values;


array_to_json


[{"item_id":1,"purchase_items_ids":[1234,1234],"total_sold":2,"discount_amount":12}]



SELECT 1


SELECT array_to_json(array_agg(p_values)) FROM 
( 
  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(pd.discount_amount) FROM purchase_discounts pd
                  WHERE pd.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 
  INNER JOIN items i ON i.id = t.item_id 
) AS p_values;


array_to_json


[{"item_id":1,"purchase_items_ids":[1234],"total_sold":1,"discount_amount":12}]



SELECT 1