Is there anyway to combine these 2 SQL queries?-CodePudding

I want to get my product details and also want to have monthly sales for each product but my sales are in different table. Is there any option to combine these 2 queries and get all in 1 query?

Products:

SELECT * 
FROM products
WHERE store_id = 'ca4172e9-5627-4867-884d-d731895c33fe'

Sales:

SELECT sku, SUM(amount) AS items, SUM(ARRAY_LENGTH(list, 1)) AS orders
FROM orders_summary
WHERE order_date BETWEEN to_timestamp(1633148000000 / 1000.0) 
                     AND to_timestamp(1633676400000 / 1000.0)
  AND sku = $1
GROUP BY sku

Right now I'm doing a for loop on my first data and then adding each sales to each row.

My products table schema:

id uuid NOT NULL,
sku text COLLATE pg_catalog."default" NOT NULL,
store_id uuid NOT NULL,
amazon_inv_available integer DEFAULT 0,
amazon_inv_total integer DEFAULT 0,
asin text COLLATE pg_catalog."default",
big_image text COLLATE pg_catalog."default" DEFAULT 'test'::text,
small_image text COLLATE pg_catalog."default" DEFAULT 'test'::text,
cost_of_unit_product real,
fba_fee money,
package_height real,
package_length real,
package_width real,
package_weight real,
product_id text COLLATE pg_catalog."default",
name text COLLATE pg_catalog."default",
referral_fee real,
package_volume real,
box_per_pallet text[] COLLATE pg_catalog."default",
comment text COLLATE pg_catalog."default",
custom real,
outer_box_width text[] COLLATE pg_catalog."default",
outer_box_height text[] COLLATE pg_catalog."default",
outer_box_length text[] COLLATE pg_catalog."default",
shipping_cost_to_amazon real,
box_upc text[] COLLATE pg_catalog."default",
unit_per_outer_box text[] COLLATE pg_catalog."default",
unit_per_inner_box text[] COLLATE pg_catalog."default",
CONSTRAINT pk_orders_id PRIMARY KEY (id),
CONSTRAINT idx_orders UNIQUE (sku),
CONSTRAINT fk_orders_stores FOREIGN KEY (store_id)
    REFERENCES public.stores (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE

My order_summary schema:

id uuid NOT NULL,
sku text COLLATE pg_catalog."default" NOT NULL,
store_id uuid NOT NULL,
order_date timestamp(0) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
asin text COLLATE pg_catalog."default" NOT NULL,
amount integer NOT NULL DEFAULT 0,
list text[] COLLATE pg_catalog."default" NOT NULL,
price money DEFAULT 0,
CONSTRAINT pk_orders_summary_id PRIMARY KEY (id),
CONSTRAINT fk_orders_summary_stores FOREIGN KEY (store_id)
    REFERENCES public.stores (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE

CodePudding user response：

Assuming that the product is identified by the sku column in table order_summary, then the query may be :

SELECT *
FROM products AS p
CROSS JOIN LATERAL 
(  SELECT sku, SUM(amount) AS items, SUM(ARRAY_LENGTH(list, 1)) AS orders
     FROM orders_summary
    WHERE order_date BETWEEN to_timestamp(1633148000000 / 1000.0) 
                         AND to_timestamp(1633676400000 / 1000.0)
    GROUP BY sku
) AS o
ON o.sku = p.sku
WHERE p.store_id = 'ca4172e9-5627-4867-884d-d731895c33fe'
  AND p.sku = $1

By the way, I would suggest you to add a foreign key in table orders_summary in order to reference the sku column in table products :

CONSTRAINT fk_orders_summary_sku FOREIGN KEY (sku)
    REFERENCES public.products (sku) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE RESTRICT -- rather than CASCADE so that to prevent the deletion of a product which has already been ordered