Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS public.products
(
    id integer NOT NULL,
    sku text COLLATE pg_catalog."default" NOT NULL,
    store_id integer NOT NULL,
    amazon_inv_available integer DEFAULT 0,
    amazon_inv_total integer DEFAULT 0,
    asin text COLLATE pg_catalog."default" DEFAULT ''::text,
    CONSTRAINT pk_orders_id PRIMARY KEY (id)
)
INSERT INTO products(id,sku,store_id,amazon_inv_available,amazon_inv_total,asin)
VALUES(1,'AC',12,100,200,'A')
1 rows affected

INSERT INTO products(id,sku,store_id,amazon_inv_available,amazon_inv_total,asin)
VALUES(2,'HH',10,100,200,'B')
1 rows affected

INSERT INTO products(id,sku,store_id,amazon_inv_available,amazon_inv_total,asin)
VALUES(3,'TT',10,100,200,'A')
1 rows affected

INSERT INTO products(id,sku,store_id,amazon_inv_available,amazon_inv_total,asin)
VALUES(4,'QW',11,100,200,'B')
1 rows affected

INSERT INTO products(id,sku,store_id,amazon_inv_available,amazon_inv_total,asin)
VALUES(5,'DD',12,100,200,'A')
1 rows affected

INSERT INTO products(id,sku,store_id,amazon_inv_available,amazon_inv_total,asin)
VALUES(6,'WE',13,100,200,'C')
1 rows affected

SELECT * FROM products


id
sku
store_id
amazon_inv_available
amazon_inv_total
asin


1
AC
12
100
200
A

2
HH
10
100
200
B

3
TT
10
100
200
A

4
QW
11
100
200
B

5
DD
12
100
200
A

6
WE
13
100
200
C


SELECT prod.asin, string_agg(p.sku,', ') AS skus,
SUM(p.amazon_inv_available) AS amazon_available_inv,
SUM(p.amazon_inv_total) AS amazon_total_inv
FROM (SELECT id, asin, sku, amazon_inv_available, amazon_inv_total
FROM products WHERE store_id IN(
12,
10,
11
)
ORDER BY sku) AS prod
LEFT JOIN
products AS p
ON prod.asin = p.asin
GROUP by prod.asin


asin
skus
amazon_available_inv
amazon_total_inv


B
HH, HH, QW, QW
400
800

A
AC, AC, AC, TT, TT, TT, DD, DD, DD
900
1800

id	sku	store_id	amazon_inv_available	amazon_inv_total	asin
1	AC	12	100	200	A
2	HH	10	100	200	B
3	TT	10	100	200	A
4	QW	11	100	200	B
5	DD	12	100	200	A
6	WE	13	100	200	C

asin	skus	amazon_available_inv	amazon_total_inv
B	HH, HH, QW, QW	400	800
A	AC, AC, AC, TT, TT, TT, DD, DD, DD	900	1800

id	sku	store_id	amazon_inv_available	amazon_inv_total	asin
1	AC	12	100	200	A
2	HH	10	100	200	B
3	TT	10	100	200	A
4	QW	11	100	200	B
5	DD	12	100	200	A
6	WE	13	100	200	C

id	sku	store_id	amazon_inv_available	amazon_inv_total	asin
1	AC	12	100	200	A
2	HH	10	100	200	B
3	TT	10	100	200	A
4	QW	11	100	200	B
5	DD	12	100	200	A
6	WE	13	100	200	C