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,'DD',13,100,200,'A')
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
DD
13
100
200
A


SELECT
  asin, 
  string_agg(sku,', ' order by id) AS skus,
  SUM(amazon_inv_available) AS amazon_available_inv,
  SUM(amazon_inv_total) AS amazon_total_inv
FROM products
WHERE store_id IN (12, 10, 11)
GROUP by asin
ORDER BY amazon_available_inv


asin
skus
amazon_available_inv
amazon_total_inv


B
HH, QW
200
400

A
AC, TT, DD
300
600

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	DD	13	100	200	A

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	DD	13	100	200	A

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	DD	13	100	200	A