CODEWITHSUNDEEP
sqlgroup-bycountsnowflake-cloud-data-platform
Dinho
Dinho

Reputation: 724

Using SQL count of multiple column values in Snowflake and transpose table output

I have a table that has 5 cols that looks like so (example):

Conf_1   | Conf_2   | Conf_3   | Conf_4   | Conf_5
High       No Match   Perfect    No Match   Perfect
High       Perfect    Review     No Match   Perfect
No Match   No Match   Perfect    Medium     Low
Perfect    Medium     No Match   Low        Medium
Low        Low        Review     Medium     High
Medium     No Match   No Match   Perfect    High

The values consist of Perfect, High, Medium, Low, No Match, and Review. Some columns contains all values and some only contain one or two of the values.

I would like to count the number of times each value shows up for each column - transpose the table into the 6 values (Perfect - Review) as the Rows and then have the count for each of the 5 Conf fields.

Desired output looks like so:

Here the confidence column is the disctinct values from the combined conf_1,conf_2......conf_5

Confidence | Conf_1 Count  | Conf_2 Count.....| Conf_5
Perfect      25               40               50 
High         25               Null             50
Medium       5                Null             50
Low          100              100              Null
No Match     4                5                Null
Review       Null             15               Null

Upvotes: 0

Views: 799

Answers (1)

trillion
trillion

Reputation: 1401

Select 
confidence,
count(case when Confidence = 'Perfect' then 1 else 0) end as conf_1,
count(case when Confidence = 'High' then 1 else 0) end as conf_2,
count(case when Confidence = 'Medium' then 1 else 0) end as conf_3,
count(case when Confidence = 'Low' then 1 else 0) end as conf_4,
count(case when Confidence = 'No Match' then 1 else 0) end as conf_5,
count(case when Confidence = 'Review' then 1 else 0) end as conf_6

from table
group by 1

You can build confidence using something like:

select * from (
select conf_1 as confidence from table
union 
select conf_2 as col from table
union
select conf_3 as col from table
union
select conf_4 as col from table
union 
select conf_5 as col from table
union
select conf_6 as col from table

) t where confidence is not null
order by confidence

Upvotes: 1

Related Questions