Counting values from many fields in a table 3

[niebs2]

Hello,
I am trying to count how many menbers have a flag in 14 flag fields in a table.I have a table called Identified and i have those 14 flag fields that can have 3 answers (Y, N, -) in them. How would i do a count for those 14 flagged fields? i can do them independently but that seems a waste of time. There has to be a easier way then doing a count for all 14 flagged fields in the table at one time. Any help is appreciated.

 

[SantaMufasa]

Niebs2,

Yes, I'm certain we can come up with a simple(r) method of counting besides comparing each field independently, but I'm not quite certain of your specifications. Are you wanting to count how many rows have at least one "Y" (out of its 14), or how many "Y"s per row, or how many "Y"s and "N"s and "-"s on each row, or what?

If you could please give some examples (and column names for the 14) columns, and a table name (and even a "CREATE TABLE <table_name>..." and a few "INSERT INTO <table_name> VALUES..." statements), that would be very helpful for us to help you better.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[niebs2]

Table name = Identified, Flag_1 to Flag_14. Want to count how many members(mbr) have a flag for each flag_1 to Flag_14.
Output would be:the count would be the members(mbr) who have a flag with a (Y, N, -). hope that helps.
Y N -
Flag_1 10 0 1
Flag_2 0 12 12
Flag_3 400 0 4000
etc

 

[SantaMufasa]

Sorry that I'm a bit dim still, Niebs2. I believe I'll need the following:
[ul][li]"CREATE TABLE <table_name>..." and a few "INSERT INTO <table_name> VALUES..." statements[/li]
[li]Results you want from the above INSERT statements.[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Andrzejek]

Could the table look something like this?
[pre]
(table) Identified

mbr Flag_1 Flag_2 Flag_3 ... Flag_13 Flag_14
Bob Y Y Y Y Y
Dave Y N N N
niebs2 ~ Y N ~ N
Susie Y N ~ ~ Y
[/pre]

Just a guess here.
Notice: Dave, Flag_13: no data - is that possible?

Have fun.

---- Andy

 

[niebs2]

Yes Andy the table could look like that. There has to be some data, hence the (-) so they are not null. So by your description the result set would be:

Count_Y, Count_N Count_dash
Flag_1 3 0 1
Flag_2 2 2 0
Flag_3 1 2 1
.
Flag_13 1 0 2
Flag_14 2 2 0

 

[Andrzejek]

Just a 'stab in the dark', long and ugly:
[tt]
Select 'Flag_1' As Whatever,
(Select Count(Flag_1)
From Identified
Where Flag_1 = 'Y') As Count_Y,
(Select Count(Flag_1)
From Identified
Where Flag_1 = 'N') As Count_N,
(Select Count(Flag_1)
From Identified
Where Flag_1 = '-') As Count_dash
From Identified
UNION
Select 'Flag_2' As Whatever,
(Select Count(Flag_2)
From Identified
Where Flag_2 = 'Y') As Count_Y,
(Select Count(Flag_2)
From Identified
Where Flag_2 = 'N') As Count_N,
(Select Count(Flag_2)
From Identified
Where Flag_2 = '-') As Count_dash
From Identified
UNION
...
Select 'Flag_14' As Whatever,
(Select Count(Flag_14)
From Identified
Where Flag_14 = 'Y') As Count_Y,
(Select Count(Flag_14)
From Identified
Where Flag_14 = 'N') As Count_N,
(Select Count(Flag_14)
From Identified
Where Flag_14 = '-') As Count_dash
From Identified
[/tt]

Have fun.

---- Andy

 

[LKBrwnDBA]

How about something like this?:

SQL> WITH tab (mbr, flag_1, flag_2, flag_3, flag_13, flag_14)
  2    AS (SELECT 'Bob   ', 'Y', 'Y', 'Y', 'Y', 'Y' FROM DUAL UNION ALL
  3        SELECT 'Dave  ', 'Y', 'N', 'N', '' , 'N' FROM DUAL UNION ALL
  4        SELECT 'niebs2', '~', 'Y', 'N', '~', 'N' FROM DUAL UNION ALL
  5        SELECT 'Susie ', 'Y', 'N', '~', '~', 'Y' FROM DUAL)
  6  SELECT YN_FLAG
  7       , SUM(case when flag = 'Y' then 1 else 0 end) flag_y
  8       , SUM(case when flag = 'N' then 1 else 0 end) flag_n
  9       , SUM(case when flag = '~' then 1 else 0 end) flag_x
 10       , SUM(case when flag is null then 1 else 0 end) flag_null
 11    FROM (
 12  SELECT *
 13    FROM tab
 14   UNPIVOT (flag FOR yn_flag IN (flag_1, flag_2, flag_3, flag_13, flag_14))
 15  )
 16  GROUP BY YN_FLAG
 17* ORDER BY YN_FLAG
SQL> /

YN_FLAG                   FLAG_Y     FLAG_N     FLAG_X  FLAG_NULL
--------------------- ---------- ---------- ---------- ----------
FLAG_1                         3          0          1          0
FLAG_13                        1          0          2          0
FLAG_14                        2          2          0          0
FLAG_2                         2          2          0          0
FLAG_3                         1          2          1          0

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

I knew there is a fancy (read: better and nicer) way to do it.

Have fun.

---- Andy

 

[LKBrwnDBA]

Corrected query (added nulls):

SQL> WITH tab (mbr, flag_1, flag_2, flag_3, flag_13, flag_14)
  2    AS (SELECT 'Bob   ', 'Y', 'Y', 'Y', 'Y', 'Y' FROM DUAL UNION ALL
  3        SELECT 'Dave  ', 'Y', 'N', 'N', '' , 'N' FROM DUAL UNION ALL
  4        SELECT 'niebs2', '~', 'Y', 'N', '~', 'N' FROM DUAL UNION ALL
  5        SELECT 'Susie ', 'Y', 'N', '~', '~', 'Y' FROM DUAL)
  6  SELECT YN_FLAG
  7       , SUM(case when flag = 'Y' then 1 else 0 end) flag_y
  8       , SUM(case when flag = 'N' then 1 else 0 end) flag_n
  9       , SUM(case when flag = '~' then 1 else 0 end) flag_x
 10       , SUM(case when flag is null then 1 else 0 end) flag_null
 11    FROM (
 12  SELECT *
 13    FROM tab
 14   UNPIVOT INCLUDE NULLS (flag FOR yn_flag IN (flag_1, flag_2, flag_3, flag_13, flag_14))
 15  )
 16  GROUP BY YN_FLAG
 17* ORDER BY YN_FLAG
SQL> /

YN_FLAG                   FLAG_Y     FLAG_N     FLAG_X  FLAG_NULL
--------------------- ---------- ---------- ---------- ----------
FLAG_1                         3          0          1          0
FLAG_13                        1          0          2          1
FLAG_14                        2          2          0          0
FLAG_2                         2          2          0          0
FLAG_3                         1          2          1          0

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

There has to be some data, hence the (-) so they are not null

Have fun.

---- Andy

 

[SantaMufasa]

El Cab,

Man, that is bad-a** !!!

In case there are readers that do not recognize it, we have just witnessed SQL Greatness in the above code. There are so many examples of excellent use of obscure SQL syntax that LKBrwn used in a single SQL statement, to solve a single business need. Everyone should bookmark this thread for its excellent referencability for use of WITH and UNPIVOT at the very least.

I do have one question/clarification, El Cab...what did your code do with the '' (NULL) in Flag_13 on Dave's row?

If I could award you multiple

s, I would, but here's the one that I can give.



Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Sorry...apparently the website from which I was linking my Purple-Star graphical reference in my above post, is dead.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[niebs2]

LKBrwnDBA - i partially get what your doing. not to be a drag here but i dont want to code the Y,N, nulls or dashes that you have with the WITH tab section. how can i get Flags to read in and sum like you have. I started doing the sum case for each flag but that seems like a waste of time. would there be another way to get the Flag_1 to Flag_14 to read in with the WITH tab you have?

Sorry i am not that technical with using SQL.

 

[Andrzejek]

I believe LKBrwnDBA was re-creating in the memory the table I posted:

[pre]
mbr Flag_1 Flag_2 Flag_3 ... Flag_13 Flag_14
Bob Y Y Y Y Y
Dave Y N N ~ N
niebs2 ~ Y N ~ N
Susie Y N ~ ~ Y

[/pre]
With this statement:

[pre]
SQL> WITH tab (mbr, flag_1, flag_2, flag_3, flag_13, flag_14)
2 AS (SELECT 'Bob ', 'Y', 'Y', 'Y', 'Y', 'Y' FROM DUAL UNION ALL
3 SELECT 'Dave ', 'Y', 'N', 'N', '' , 'N' FROM DUAL UNION ALL
4 SELECT 'niebs2', '~', 'Y', 'N', '~', 'N' FROM DUAL UNION ALL
5 SELECT 'Susie ', 'Y', 'N', '~', '~', 'Y' FROM DUAL)
[/pre]

And if your table is called “[blue]Identified[/blue]” and fields you are going after are: Flag_1 Flag_2 Flag_3 all the way to Flag_14, you would just need this:

[pre]
6 SELECT YN_FLAG
7 , SUM(case when flag = 'Y' then 1 else 0 end) flag_y
8 , SUM(case when flag = 'N' then 1 else 0 end) flag_n
9 , SUM(case when flag = '~' then 1 else 0 end) flag_x
10 , SUM(case when flag is null then 1 else 0 end) flag_null
11 FROM (
12 SELECT *
13 FROM [blue]Identified[/blue]
14 UNPIVOT INCLUDE NULLS (flag FOR yn_flag IN (flag_1, flag_2, flag_3, flag_13, flag_14))
15 )
16 GROUP BY YN_FLAG
17* ORDER BY YN_FLAG
[/pre]

Have fun.

---- Andy

 

[LKBrwnDBA]

Thanks Mufasa,

I had to use "UNPIVOT INCLUDE NULLS" option for the nulls to be counted under "FLAG_NULL" column.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[niebs2]

Thank you all for helping me out. It works and i want to say Thank you again for helping me understand more SQL.