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

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

CodePudding user response：

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

CodePudding user response：

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