By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH t AS
(
SELECT 'US' AS country, 2 AS shop, 50 AS sales, 5 AS price FROM dual
UNION ALL
SELECT 'CN' AS country, 2 AS shop, 5 AS sales, 7 AS price FROM dual
UNION ALL
SELECT 'US' AS country, 3 AS shop, 50 AS sales, 5 AS price FROM dual
UNION ALL
SELECT 'CN' AS country, 3 AS shop, 5 AS sales, 5 AS price FROM dual
UNION ALL
SELECT 'US' AS country, 2 AS shop, 50 AS sales, 5 AS price FROM dual
UNION ALL
SELECT 'CN' AS country, 2 AS shop, 5 AS sales, 5 AS price FROM dual
UNION ALL
SELECT 'US' AS country, 3 AS shop, 10 AS sales, 5 AS price FROM dual
UNION ALL
SELECT 'CN' AS country, 3 AS shop, 5 AS sales, 5 AS price FROM dual
)
SELECT shop, AVG(price) AS avg_price, SUM("US") AS us, SUM("CN") AS cn
FROM t
PIVOT ( SUM(sales) for country in ( 'US' AS "US",'CN' AS "CN" ))
GROUP BY shop
| SHOP | AVG_PRICE | US | CN |
|---|---|---|---|
| 2 | 6 | 100 | 10 |
| 3 | 5 | 60 | 10 |