INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Counting values from many fields in a table

Counting values from many fields in a table

(OP)
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.

RE: Counting values from many fields in a table

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,

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

RE: Counting values from many fields in a table

(OP)
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

RE: Counting values from many fields in a table

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

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

RE: Counting values from many fields in a table

Could the table look something like this?
(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
 

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

Have fun.

---- Andy

RE: Counting values from many fields in a table

(OP)
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

RE: Counting values from many fields in a table

Just a 'stab in the dark', long and ugly:

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

Have fun.

---- Andy

RE: Counting values from many fields in a table





How about something like this?:

CODE

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 
3eyes



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

RE: Counting values from many fields in a table

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

Have fun.

---- Andy

RE: Counting values from many fields in a table

2

Corrected query (added nulls):

CODE

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 
noevil

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

RE: Counting values from many fields in a table

Quote (niebs2 on 2 Oct 13 10:24)

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

Have fun.

---- Andy

RE: Counting values from many fields in a table

El Cab,

Quote (My 17-year-old grandson)

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.

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

RE: Counting values from many fields in a table

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

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

RE: Counting values from many fields in a table

(OP)
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.

RE: Counting values from many fields in a table

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

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

 
With this statement:

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)
 

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

  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 Identified
 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
 

Have fun.

---- Andy

RE: Counting values from many fields in a table


Thanks Mufasa,

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

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

RE: Counting values from many fields in a table

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close