Pivot Multiple Columns in Snowflake

1
votes

Unable to pivot multiple columns in snowflake.

This works:

--DROP TABLE "PUBLIC".MONTHLY_SALES
create or replace table monthly_sales(empid int, amount int, month text)
    as select * from values
    (1, 10000, 'JAN'),
    (1, 400, 'JAN'),
    (2, 4500, 'JAN'),
    (2, 35000, 'JAN'),
    (1, 5000, 'FEB'),
    (1, 3000, 'FEB'),
    (2, 200, 'FEB'),
    (2, 90500, 'FEB'),
    (1, 6000, 'MAR'),
    (1, 5000, 'MAR'),
    (2, 2500, 'MAR'),
    (2, 9500, 'MAR'),
    (1, 8000, 'APR'),
    (1, 10000, 'APR'),
    (2, 800, 'APR'),
    (2, 4500, 'APR');
    
   
 SELECT * FROM monthly_sales
 pivot(
        sum(amount)
        for month in ('JAN', 'FEB', 'MAR', 'APR')
        ) AS p;

But I receive an error when adding an additional aggregate

SELECT * FROM monthly_sales
 pivot(
        sum(amount)
        , count(amount)
        for month in ('JAN', 'FEB', 'MAR', 'APR')
        ) AS p;

QL Error [1003] [42000]: SQL compilation error: syntax error line 4 at position 5 unexpected ','. syntax error line 4 at position 12 unexpected '('. syntax error line 5 at position 45 unexpected ')'.

Any guidance is appreciated

2
sqlpivotsnowflake-cloud-data-platform

2 Answers

4
votes

Felipe is right, you can only use one aggregate in PIVOT

But based on what you are trying to achieve, this query might be helpful.

SELECT 'SUM' RECORD_TYPE, * FROM (SELECT * FROM monthly_sales
 pivot(
        SUM(amount) 
        for month in ('JAN', 'FEB', 'MAR', 'APR')
        ) AS p)
UNION ALL
SELECT 'COUNT', * FROM (SELECT * FROM monthly_sales
 pivot(
        count(amount) 
        for month in ('JAN', 'FEB', 'MAR', 'APR')
        ) AS p)

Output:

Row RECORD_TYPE EMPID   'JAN'     'FEB'   'MAR'  'APR'
1     SUM           1   10400      8000   11000  18000
2     SUM           2   39500     90700   12000   5300
3     COUNT         1       2         2       2      2
4     COUNT         2       2         2       2      2
1
votes

According to the documentation of PIVOT in Snowflake, you can only get one aggregate:

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )

[ ... ]

If you post a different question asking with sample input and output, we can try to work out a solution for this.

As for this question, I don't know how I would represent two different aggregates in a relational table. But at least we can answer why you are getting a syntax error: Multiple aggregates is not a supported syntax by PIVOT.