CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
asd
asd

Reputation: 183

How to do Pivot (Count (*) ) functionality in Snowflake

My Query is as follows:

I am getting an error "unexpected '*'." at line 6, please let me know , how to rewrite or correct this issue

select * from (SELECT gl gl_acc,
                               reg reg,
                               mapp map_name,
                               field as f1
                          FROM gl_acc a
                         WHERE TYPE = 'AGG') PIVOT (count(*)
                                             FOR f1
                                             IN  (
                                                 'JOURN' ,
                                                 'JOU' ,
                                                 'J' ,
                                                 'CASE_' ,
                                                 'JOUR' 
                                                ))as p;

Thanks, Goe

Upvotes: 1

Views: 1404

Answers (2)

Rajat
Rajat

Reputation: 5803

I would go with conditional aggregation as well. It's easier to read and to maintain. To get your code to work, you need to specify what exactly you're counting in the count function. You didn't provide a sample, but here is a working code for you to see how that works

with cte (num0,num1,str1) as

(select 1,1,'a' union all
 select 2,1,'a')
 
 select *
 from cte
 pivot (count(num1) for str1 in ('a', 'b')) as t(col1, col2, col2)

Notice how you can avoid that subquery by using the as clause from your pivot to define custom column names.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Just use conditional aggregation:

SELECT gl, reg, mapp,
       SUM(CASE WHEN field = 'JOURN' THEN 1 ELSE 0 END) as journ,
       SUM(CASE WHEN field = 'JOU' THEN 1 ELSE 0 END) as jou,
       SUM(CASE WHEN field = 'J' THEN 1 ELSE 0 END) as j,
       SUM(CASE WHEN field = 'CASE_' THEN 1 ELSE 0 END) as case_,
       SUM(CASE WHEN field = 'JOUR' THEN 1 ELSE 0 END) as jour       
FROM gl_acc a
WHERE TYPE = 'AGG'
GROUP BY gl, reg, mapp;

Or more simply using COUNT_IF()

SELECT gl, reg, mapp,
       COUNT_IF(field = 'JOURN') as journ,
       COUNT_IF(field = 'JOU') as jou,
       COUNT_IF(field = 'J') as j,
       COUNT_IF(field = 'CASE_') as case_,
       COUNT_IF(field = 'JOUR') as jour       
FROM gl_acc a
WHERE TYPE = 'AGG'
GROUP BY gl, reg, mapp;

Upvotes: 2

Related Questions