CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Aaron
Aaron

Reputation: 1455

VALUES in Snowflake

I have a table like so -

n0   n1  n2
232  12  211
323  45  5655

I want to produce this output -

n    Count
232  1
12   1 
211  1
323  1
45   1
5655 1

I can do this using this query in PostgreSQL.

select el, count(*)
from 
(
SELECT  n0, n1, n2 from npi
) n
cross join lateral
     (select v.el
      from (values (n0), (n1), (n2)) v(el)
     ) v
group by el

But when I try to run the same query in Snowflake, I get this error -

Invalid expression [CORRELATION(N.N0)] in VALUES clause

Upvotes: 1

Views: 360

Answers (2)

Eric Lin
Eric Lin

Reputation: 1530

Below is the version using UNPIVOT:

create or replace table test (n0 int, n1 int, n2 int);

insert into test values (232, 12, 211), (323, 45, 5655);

select value, count(1) from test
unpivot(value for col_name in (n0, n1, n2))
group by value;

+-------+----------+
| VALUE | COUNT(1) |
|-------+----------|
|   232 |        1 |
|    12 |        1 |
|   211 |        1 |
|   323 |        1 |
|    45 |        1 |
|  5655 |        1 |
+-------+----------+

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

Alaternative form using UNION ALL:

WITH cte(el) AS (
  SELECT n0 FROM npi
  UNION ALL SELECT n1 FROM npi
  UNION ALL SELECT n2 FROM npi
)
SELECT el, COUNT(*)
FROM cte
GROUP BY el;

Output:

enter image description here

EDIT:

A version that uses FLATTEN combined with ARRAY_CONSTRUCT:

SELECT value AS el, COUNT(*)
FROM(SELECT  n0, n1, n2 from npi) AS n
,TABLE(FLATTEN(input => array_construct(n.n0, n.n1, n.n2)))
GROUP BY value;

Upvotes: 1

Related Questions