PL SQL Pivot: use different aggregates

I want to pivot sum(sales) per country as below. But the price column should be an average per shop. So, I would love to have this result:

Desired Result:

shop	avg_price	US	CN
2	6	100	10
3	5	60	10

Current Result:

shop	price	US	CN
2	5	null	5
2	7	100	5
3	5	60	10

I could skip price within the pivot part and simply rejoin it later on. However, I believe there is a smarter option. Can you help me out?

Query (so far)

SELECT *
  FROM (
         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
       )
 PIVOT ( SUM(sales) FOR country IN ( 'US','CN' )
    );

Answer

You need to group by shop while adding double-quoted aliases for each pivoted columns such as

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

Demo

Advertisement

Answer