I want to count distinct values in multiple column and show the results in separate rows

user_18373998 0
Viewed 43

I'm kinda new to SQL and working on a dataset where I need to count 1,0 or Y,N flags in each of the column and give a resultant count column. To give an example, here's what the data looks like:

Flag1 Flag2 Flag3 Flag4
N Y N Y
Y Y Y Y
N N N N
Y Y N N

Here's what I want:

Flag1 Flag2 Flag3 Flag4 COUNT
N 2
Y 2
N 1
Y 3
N 3
Y 1
N 2
Y 2
snowflake-cloud-data-platform sql
2 Answers

There is a much simpler way to calculate this using the GROUP BY GROUPING SETS function:

WITH sample_data AS (
  SELECT * 
    FROM (VALUES 
  ('N', 'Y', 'N', 'Y'),
  ('Y', 'Y', 'Y', 'Y'),
  ('N', 'N', 'N', 'N'),
  ('Y', 'Y', 'N', 'N')) T(Flag1, Flag2, Flag3, Flag4)
)
SELECT Flag1, Flag2, Flag3, Flag4, COUNT(*) AS COUNT
  FROM sample_data
 GROUP BY GROUPING SETS (Flag1, Flag2, Flag3, Flag4)
 ORDER BY Flag1, Flag2, Flag3, Flag4;

Result:

FLAG1 FLAG2 FLAG3 FLAG4 COUNT
N 2
Y 2
N 1
Y 3
N 3
Y 1
N 2
Y 2
user_126071260

You just need a union between all the possible combinations:

select 'N' as Flag1, NULL as Flag2, NULL as Flag3, NULL as Flag4, (select count(*) from table_name where Flag1='N') as count
union all
select 'Y', NULL, NULL, NULL, (select count(*) from table_name where Flag1='Y') as count

union all

select NULL, 'N', NULL, NULL, (select count(*) from table_name where Flag2='N') as count
union all
select NULL, 'Y', NULL, NULL, (select count(*) from table_name where Flag2='Y') as count

union all

select NULL, NULL, 'N', NULL, (select count(*) from table_name where Flag3='N') as count
union all
select NULL, NULL, 'Y', NULL, (select count(*) from table_name where Flag3='Y') as count

union all

select NULL, NULL, NULL, 'N', (select count(*) from table_name where Flag4='N') as count
union all
select NULL, NULL, NULL, 'Y', (select count(*) from table_name where Flag4='Y') as count
user_176450990
Related