Home » SQL & PL/SQL » SQL & PL/SQL » Count group data (12.1.0.2)
Count group data [message #653332] Sat, 02 July 2016 02:37 Go to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Hello,

I need to convert a table from binary number data to decimal number data by counting same binary number on each group on the same row and insert the result into a new table as below.

-- Example convert from row A to B 
A: 0	0	0	1	1	0	1	0	1	1	0	0	0	0
B: 3			2		1	1	1	2		4			




-- The binary number data table "DataBN" has the follow structure.

DataBN	SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
	1	1	1	0	1	0	0	0	0	1	0	1	1	1
	2	1	0	0	1	0	1	0	0	1	0	1	0	1
	3	1	0	0	0	0	1	0	1	0	0	0	0	0


-- The decimal number data "DataDN" should look similar below after converting from table DataBN.

DataDN	SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
	1	2	1	1	1	4	1	0	3					
	2	2	2	1	1	1	2	1	1	1	1	1		
	3	2	4	1	0	1	5							

Any help would be greatly appreciated.
Re: Count group data [message #653334 is a reply to message #653332] Sat, 02 July 2016 02:47 Go to previous messageGo to next message
John Watson
Messages: 8624
Registered: January 2010
Location: Global Village
Senior Member
I don't understand this "conversion" at all. What is the algorithm that you are applying?

Also, you need to provide the CREATE TABLE STATEMENTS and the INSERT statements needed to set up the problem.
Re: Count group data [message #653336 is a reply to message #653332] Sat, 02 July 2016 03:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Your sample conversion is inconsistent. In some cases, you are counting zeroes and in other cases you show a 0 value. I will assume that you should be counting zeroes.

-- starting data:
SCOTT@orcl_12.1.0.2.0> select * from databn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0

3 rows selected.

-- ctas and results:
SCOTT@orcl_12.1.0.2.0> create table datadn as
  2  select sid, tid,
  3  	    sum (decode (groups, 1, dec, null)) c1,
  4  	    sum (decode (groups, 2, dec, null)) c2,
  5  	    sum (decode (groups, 3, dec, null)) c3,
  6  	    sum (decode (groups, 4, dec, null)) c4,
  7  	    sum (decode (groups, 5, dec, null)) c5,
  8  	    sum (decode (groups, 6, dec, null)) c6,
  9  	    sum (decode (groups, 7, dec, null)) c7,
 10  	    sum (decode (groups, 8, dec, null)) c8,
 11  	    sum (decode (groups, 9, dec, null)) c9,
 12  	    sum (decode (groups, 10, dec, null)) c10,
 13  	    sum (decode (groups, 11, dec, null)) c11,
 14  	    sum (decode (groups, 12, dec, null)) c12
 15  from   (select sid, tid, groups, count(*) dec
 16  	     from   (select sid, tid, col, val, flag,
 17  			    sum (flag) over (partition by sid, tid order by col) groups
 18  		     from   (select sid, tid, col, val,
 19  				    decode (val, lag(val) over (partition by sid, tid order by col), 0, 1) flag
 20  			     from   (select sid, tid, 1 col, c1 val from databn union all
 21  				     select sid, tid, 2 col, c2 val from databn union all
 22  				     select sid, tid, 3 col, c3 val from databn union all
 23  				     select sid, tid, 4 col, c4 val from databn union all
 24  				     select sid, tid, 5 col, c5 val from databn union all
 25  				     select sid, tid, 6 col, c6 val from databn union all
 26  				     select sid, tid, 7 col, c7 val from databn union all
 27  				     select sid, tid, 8 col, c8 val from databn union all
 28  				     select sid, tid, 9 col, c9 val from databn union all
 29  				     select sid, tid, 10 col, c10 val from databn union all
 30  				     select sid, tid, 11 col, c11 val from databn union all
 31  				     select sid, tid, 12 col, c12 val from databn)))
 32  	     group by sid, tid, groups)
 33  group by sid, tid
 34  order by sid, tid
 35  /

Table created.

SCOTT@orcl_12.1.0.2.0> select * from datadn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          4          1          1          3
         2          1          2          1          1          1          2          1          1          1          1          1
         3          1          4          1          1          1          5

3 rows selected.
Re: Count group data [message #653337 is a reply to message #653332] Sat, 02 July 2016 04:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Another method:

SCOTT@orcl_12.1.0.2.0> create table datadn as
  2  select *
  3  from   (select sid, tid, groups, count(*) dec
  4  	     from   (select sid, tid, col, val, flag,
  5  			    sum (flag) over (partition by sid, tid order by col) groups
  6  		     from   (select sid, tid, to_number(col) col, val,
  7  				    decode (val, lag(val) over (partition by sid, tid order by to_number(col)), 0, 1) flag
  8  			     from   (select sid, tid,
  9  					    decode (col, null, null, to_number(substr(col, 2))) as col, val
 10  				     from   databn
 11  				     unpivot include nulls
 12  					   (val for col in (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12)))))
 13  	     group  by sid, tid, groups)
 14  pivot (sum (dec) for groups in
 15  	      (1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6,
 16  	       7 as c7, 8 as c8, 9 as c9, 10 as c10, 11 as c11, 12 as c12))
 17  order  by sid, tid
 18  /

Table created.

SCOTT@orcl_12.1.0.2.0> select * from datadn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          4          1          1          3
         2          1          2          1          1          1          2          1          1          1          1          1
         3          1          4          1          1          1          5

3 rows selected.
Re: Count group data [message #653339 is a reply to message #653332] Sat, 02 July 2016 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remind:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Count group data [message #653345 is a reply to message #653339] Sun, 03 July 2016 00:57 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Thanks much Barbara. I tested both statements and they are both working with the same result.

Sorry for the confusion. There is a typo in the sample. Yes I do need to count all zeroes as well. I tried to make the correction of the sample data but unsuccessfully.

I noticed that the DataDN table is created with the same TID as the DataBN table. The DataDN table should have the TID of 2. Also if a new data row with SID #4 has been added to DataBN table, is there a way to just convert that new row. Your advice is much appreciated.
Re: Count group data [message #653346 is a reply to message #653345] Sun, 03 July 2016 01:30 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara,

Look like it also counted the blank cells as I tested with SID #4 below. I have many rows with null at the end of the row which I don't want to count those.

Thank you again for all your help!






-- DataBN table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	1	1	1	0	0	0	0	1	0	1	1	1	1
2	1	0	1	1	0	0	0	1	0	0	1	0	0
3	1	1	0	0	0	1	0	1	1	1	0	1	0
4	1	0	1	1	0	1	0	0	0	1			



-- DataDN table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	1	2	4	1	1	4							
2	1	1	2	3	1	2	1	2					
3	1	1	3	1	1	3	1	1	1				
4	1	1	2	1	1	3	1	3					

[Updated on: Sun, 03 July 2016 01:30]

Report message to a moderator

Re: Count group data [message #653347 is a reply to message #653346] Sun, 03 July 2016 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from DataBN ;
select * from DataBN
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from DataDN ;
select * from DataDN
              *
ERROR at line 1:
ORA-00942: table or view does not exist
Re: Count group data [message #653350 is a reply to message #653346] Sun, 03 July 2016 14:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Sorry I didn't notice that the tid's should have been 2, but that's easy to fix.

It is also easy to change it so that it does not count the null values.

As to just adding a new row to datadn when a new row is inserted into databn, there are various ways to handle that:

You could write an insert statement using some criteria, such as that the sid in databn is greater than the maximum sid in datadn or

you could write a before insert row trigger on the databn table that would automatically insert the data into the datadn table or

you could create a view, which is what I recommend. Since the datadn table does not contain any data that is not calculated from the databn table, it is generally a better practice not to duplicate data storage, and to just create a view to select from. You can use the same code for creating the view as you would for creating the table. When data is inserted, the next time that you select from the view, the data will show. Please see the demonstration below. I have provided separate demonstrations for each of the 2 previous query types. If you do this, you will not need a datadn table at all, just the view to replace it.

SCOTT@orcl_12.1.0.2.0> create or replace view datadn as
  2  select sid, 2 tid,
  3  	    sum (decode (groups, 1, dec, null)) c1,
  4  	    sum (decode (groups, 2, dec, null)) c2,
  5  	    sum (decode (groups, 3, dec, null)) c3,
  6  	    sum (decode (groups, 4, dec, null)) c4,
  7  	    sum (decode (groups, 5, dec, null)) c5,
  8  	    sum (decode (groups, 6, dec, null)) c6,
  9  	    sum (decode (groups, 7, dec, null)) c7,
 10  	    sum (decode (groups, 8, dec, null)) c8,
 11  	    sum (decode (groups, 9, dec, null)) c9,
 12  	    sum (decode (groups, 10, dec, null)) c10,
 13  	    sum (decode (groups, 11, dec, null)) c11,
 14  	    sum (decode (groups, 12, dec, null)) c12
 15  from   (select sid, tid, groups, count(val) dec
 16  	     from   (select sid, tid, col, val, flag,
 17  			    sum (flag) over (partition by sid, tid order by col) groups
 18  		     from   (select sid, tid, col, val,
 19  				    decode (val, lag(val) over (partition by sid, tid order by col), 0, 1) flag
 20  			     from   (select sid, tid, 1 col, c1 val from databn where c1 is not null union all
 21  				     select sid, tid, 2 col, c2 val from databn where c2 is not null union all
 22  				     select sid, tid, 3 col, c3 val from databn where c3 is not null union all
 23  				     select sid, tid, 4 col, c4 val from databn where c4 is not null union all
 24  				     select sid, tid, 5 col, c5 val from databn where c5 is not null union all
 25  				     select sid, tid, 6 col, c6 val from databn where c6 is not null union all
 26  				     select sid, tid, 7 col, c7 val from databn where c7 is not null union all
 27  				     select sid, tid, 8 col, c8 val from databn where c8 is not null union all
 28  				     select sid, tid, 9 col, c9 val from databn where c9 is not null union all
 29  				     select sid, tid, 10 col, c10 val from databn where c10 is not null union all
 30  				     select sid, tid, 11 col, c11 val from databn where c11 is not null union all
 31  				     select sid, tid, 12 col, c12 val from databn where c12 is not null)))
 32  	     group by sid, tid, groups)
 33  group by sid, tid
 34  order by sid, tid
 35  /

View created.

SCOTT@orcl_12.1.0.2.0> select * from databn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0

3 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datadn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          1          1          1          4          1          1          3
         2          2          2          1          1          1          2          1          1          1          1          1
         3          2          4          1          1          1          5

3 rows selected.

SCOTT@orcl_12.1.0.2.0> insert into databn values ( 4, 1, 0, 1, 1, 0, 1, 0, 0, 0, 1, null, null, null)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> select * from databn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0
         4          1          0          1          1          0          1          0          0          0          1

4 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datadn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          1          1          1          4          1          1          3
         2          2          2          1          1          1          2          1          1          1          1          1
         3          2          4          1          1          1          5
         4          2          1          2          1          1          3          1

4 rows selected.

-- reset between demonstrations
SCOTT@orcl_12.1.0.2.0> rollback
  2  /

Rollback complete.

SCOTT@orcl_12.1.0.2.0> drop view datadn
  2  /

View dropped.

SCOTT@orcl_12.1.0.2.0> create or replace view datadn as
  2  select *
  3  from   (select sid, tid, groups, count(*) dec
  4  	     from   (select sid, tid, col, val, flag,
  5  			    sum (flag) over (partition by sid, tid order by col) groups
  6  		     from   (select sid, tid, to_number(col) col, val,
  7  				    decode (val, lag(val) over (partition by sid, tid order by to_number(col)), 0, 1) flag
  8  			     from   (select sid, 2 tid,
  9  					    decode (col, null, null, to_number(substr(col, 2))) as col, val
 10  				     from   databn
 11  				     unpivot
 12  					   (val for col in (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12)))))
 13  	     group  by sid, tid, groups)
 14  pivot (sum (dec) for groups in
 15  	      (1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6,
 16  	       7 as c7, 8 as c8, 9 as c9, 10 as c10, 11 as c11, 12 as c12))
 17  order  by sid, tid
 18  /

View created.

SCOTT@orcl_12.1.0.2.0> select * from databn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0

3 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datadn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          1          1          1          4          1          1          3
         2          2          2          1          1          1          2          1          1          1          1          1
         3          2          4          1          1          1          5

3 rows selected.

SCOTT@orcl_12.1.0.2.0> insert into databn values ( 4, 1, 0, 1, 1, 0, 1, 0, 0, 0, 1, null, null, null)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> select * from databn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0
         4          1          0          1          1          0          1          0          0          0          1

4 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datadn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          1          1          1          4          1          1          3
         2          2          2          1          1          1          2          1          1          1          1          1
         3          2          4          1          1          1          5
         4          2          1          2          1          1          3          1

4 rows selected.
Re: Count group data [message #653441 is a reply to message #653350] Tue, 05 July 2016 11:27 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara, Thanks much. I tested again both latest statements working perfectly. I like your recommendation to use view for not to duplicate data storage.
There is one problem when I looks at data of the "datadn" table that if there are 2 counts of #2 on the same row sid2 or 5 counts of #1 on row sid1. I don't know which of #2 of (1,1) or (0,0). So to prevent this confusion, is there a way to add the count number in the front of the binary number as datadn2 table below. Your advice is much appreciated.

-- databn table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	1	1	0	1	0	0	0	0	1	0	1	1	1
2	1	0	0	1	0	1	0	0	1	0	1	0	1
3	1	0	0	0	0	1	0	1	0	0	0	0	0
4	1	0	1	1	0	1	0	0	0	1			

-- datadn2 table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	2	11	10	11	40	11	10	31					
2	2	20	11	10	11	20	11	10	11	10	11		
3	2	40	11	10	11	50							
4	2	10	21	10	11	30	11						

[Updated on: Tue, 05 July 2016 11:28]

Report message to a moderator

Re: Count group data [message #653447 is a reply to message #653441] Tue, 05 July 2016 15:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I didn't understand this:
Quote:

... when I looks at data of the "datadn" table that if there are 2 counts of #2 on the same row sid2 or 5 counts of #1 on row sid1. I don't know which of #2 of (1,1) or (0,0)....


but I understood this and the results:
Quote:

... is there a way to add the count number in the front of the binary number as datadn2 table below....


I have demonstrated viewss using two different queries below.

-- test data:
SCOTT@orcl_12.1.0.2.0> select * from databn
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0
         4          1          0          1          1          0          1          0          0          0          1

4 rows selected.

-- first view:
SCOTT@orcl_12.1.0.2.0> create or replace view datadn2 as
  2  select sid, 2 tid,
  3  	    sum (decode (groups, 1, dec || val, null)) c1,
  4  	    sum (decode (groups, 2, dec || val, null)) c2,
  5  	    sum (decode (groups, 3, dec || val, null)) c3,
  6  	    sum (decode (groups, 4, dec || val, null)) c4,
  7  	    sum (decode (groups, 5, dec || val, null)) c5,
  8  	    sum (decode (groups, 6, dec || val, null)) c6,
  9  	    sum (decode (groups, 7, dec || val, null)) c7,
 10  	    sum (decode (groups, 8, dec || val, null)) c8,
 11  	    sum (decode (groups, 9, dec || val, null)) c9,
 12  	    sum (decode (groups, 10, dec || val, null)) c10,
 13  	    sum (decode (groups, 11, dec || val, null)) c11,
 14  	    sum (decode (groups, 12, dec || val, null)) c12
 15  from   (select sid, tid, groups, val, count(val) dec
 16  	     from   (select sid, tid, col, val, flag,
 17  			    sum (flag) over (partition by sid, tid order by col) groups
 18  		     from   (select sid, tid, col, val,
 19  				    decode (val, lag(val) over (partition by sid, tid order by col), 0, 1) flag
 20  			     from   (select sid, tid, 1 col, c1 val from databn where c1 is not null union all
 21  				     select sid, tid, 2 col, c2 val from databn where c2 is not null union all
 22  				     select sid, tid, 3 col, c3 val from databn where c3 is not null union all
 23  				     select sid, tid, 4 col, c4 val from databn where c4 is not null union all
 24  				     select sid, tid, 5 col, c5 val from databn where c5 is not null union all
 25  				     select sid, tid, 6 col, c6 val from databn where c6 is not null union all
 26  				     select sid, tid, 7 col, c7 val from databn where c7 is not null union all
 27  				     select sid, tid, 8 col, c8 val from databn where c8 is not null union all
 28  				     select sid, tid, 9 col, c9 val from databn where c9 is not null union all
 29  				     select sid, tid, 10 col, c10 val from databn where c10 is not null union all
 30  				     select sid, tid, 11 col, c11 val from databn where c11 is not null union all
 31  				     select sid, tid, 12 col, c12 val from databn where c12 is not null)))
 32  	     group by sid, tid, val, groups)
 33  group by sid, tid
 34  /

View created.

SCOTT@orcl_12.1.0.2.0> select * from datadn2 order by sid, tid
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2         11         10         11         40         11         10         31
         2          2         20         11         10         11         20         11         10         11         10         11
         3          2         40         11         10         11         50
         4          2         10         21         10         11         30         11

4 rows selected.

-- second view:
SCOTT@orcl_12.1.0.2.0> drop view datadn2
  2  /

View dropped.

SCOTT@orcl_12.1.0.2.0> create or replace view datadn2 as
  2  select *
  3  from   (select sid, tid, groups, cast ((count(*) || val) as varchar2(10)) dec
  4  	     from   (select sid, tid, col, val, flag,
  5  			    sum (flag) over (partition by sid, tid order by col) groups
  6  		     from   (select sid, tid, to_number(col) col, val,
  7  				    decode (val, lag(val) over (partition by sid, tid order by to_number(col)), 0, 1) flag
  8  			     from   (select sid, 2 tid,
  9  					    decode (col, null, null, to_number(substr(col, 2))) as col, val
 10  				     from   databn
 11  				     unpivot
 12  					   (val for col in (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12)))))
 13  	     group  by sid, tid, groups, val)
 14  pivot (max (dec) for groups in
 15  	      (1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6,
 16  	       7 as c7, 8 as c8, 9 as c9, 10 as c10, 11 as c11, 12 as c12))
 17  /

View created.

SCOTT@orcl_12.1.0.2.0> select * from datadn2 order by sid, tid
  2  /

       SID        TID C1         C2         C3         C4         C5         C6         C7         C8         C9         C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2 11         10         11         40         11         10         31
         2          2 20         11         10         11         20         11         10         11         10         11
         3          2 40         11         10         11         50
         4          2 10         21         10         11         30         11

4 rows selected.
Re: Count group data [message #653480 is a reply to message #653447] Wed, 06 July 2016 20:46 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara, Thanks much again and sorry about the confusion. You got the corrected results as putting the count number in the front of the binary number in table "datadn2". From the data in table DataBN and DataDN, I need to calculate total binary number in the way as shown in the table DataCN. Your advice is much appreciated.


-- DataBN Table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	1	1	0	1	0	0	0	0	1	0	1	1	1
2	1	0	0	1	0	1	0	0	1	0	1	0	1
3	1	0	0	0	0	1	0	1	0	0	0	0	0
4	1	0	1	1	0	1	0	0	0	1			

-- DataDN Table or view
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	2	1	1	1	4	1	1	3					
2	2	2	1	1	1	2	1	1	1	1	1		
3	2	4	1	1	1	5							
4	2	1	2	1	1	3	1						

-- DataCN Table or view
SID	TID	TCA	TCC	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	3	12	7	5	0	1	1	0	0	0	0	0	0	0	0
2	3	12	10	8	2	0	0	0	0	0	0	0	0	0	0
3	3	12	5	3	0	0	1	1	0	0	0	0	0	0	0
4	3	9	6	4	1	1	0	0	0	0	0	0	0	0	0

-- Example Calculation of row SID #1 of DataCN table
TCA	=	12	Total count of all Binary number of Table DataBN (C1 to C12)
TCC	=	7	Total count of correct Binary number of Table DataDN (C1 to C12)
C1	=	5	Total count of all #1 of table DataDN
C2	=	0	Total count of all #2 of table DataDN
C3	=	1	Total count of all #3 of table DataDN
C4	=	1	Total count of all #4 of table DataDN
C5	=	0	Total count of all #5 of table DataDN
C6	=	0	Total count of all #6 of table DataDN
C7	=	0	Total count of all #7 of table DataDN
C8	=	0	Total count of all #8 of table DataDN
C9	=	0	Total count of all #9 of table DataDN
C10	=	0	Total count of all #10 of table DataDN
C11	=	0	Total count of all #11 of table DataDN
C12	=	0	Total count of all #12 of table DataDN

Re: Count group data [message #653481 is a reply to message #653480] Wed, 06 July 2016 22:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
-- test data:
SCOTT@orcl_12.1.0.2.0> select * from databn order by sid
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0
         4          1          0          1          1          0          1          0          0          0          1

4 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datadn order by sid
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          1          1          1          4          1          1          3
         2          2          2          1          1          1          2          1          1          1          1          1
         3          2          4          1          1          1          5
         4          2          1          2          1          1          3          1

4 rows selected.

-- select statement that can be used to create table or view:
SCOTT@orcl_12.1.0.2.0> select b.sid, 3 tid, b.tca, d.tcc,
  2  	    d.c1,d.c2,d.c3,d.c4,d.c5,d.c6,d.c7,d.c8,d.c9,d.c10,d.c11,d.c12
  3  from   (select sid, sum(nvl2(val,1,0)) tca
  4  	     from   (select sid, col, val
  5  		     from   databn
  6  		     unpivot include nulls (val for col in
  7  		       (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)))
  8  	     group  by sid) b,
  9  	    (select sid, sum(nvl2(val,1,0)) tcc,
 10  		    sum(decode(val,1,1,0)) c1,
 11  		    sum(decode(val,2,1,0)) c2,
 12  		    sum(decode(val,3,1,0)) c3,
 13  		    sum(decode(val,4,1,0)) c4,
 14  		    sum(decode(val,5,1,0)) c5,
 15  		    sum(decode(val,6,1,0)) c6,
 16  		    sum(decode(val,7,1,0)) c7,
 17  		    sum(decode(val,8,1,0)) c8,
 18  		    sum(decode(val,9,1,0)) c9,
 19  		    sum(decode(val,10,1,0)) c10,
 20  		    sum(decode(val,11,1,0)) c11,
 21  		    sum(decode(val,12,1,0)) c12
 22  	     from   (select sid, col, val
 23  		     from   datadn
 24  		     unpivot include nulls (val for col in
 25  			       (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)))
 26  	     group  by sid) d
 27  where  b.sid = d.sid
 28  order  by sid
 29  /

       SID        TID        TCA        TCC         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          3         12          7          5          0          1          1          0          0          0          0          0          0          0          0
         2          3         12         10          8          2          0          0          0          0          0          0          0          0          0          0
         3          3         12          5          3          0          0          1          1          0          0          0          0          0          0          0
         4          3          9          6          4          1          1          0          0          0          0          0          0          0          0          0

4 rows selected.
Re: Count group data [message #653533 is a reply to message #653481] Sat, 09 July 2016 17:14 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Thanks much Barbara! After reviewing the result data from your select statement, I realized that I did not include all binary numbers in the count. Such as a count of 4 should include 1, 2, 3, and 4. Below is the calculation including those binary numbers. Your advice is much appreciated.


-- DataBN Table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	1	1	0	1	0	0	0	0	1	0	1	1	1
2	1	0	0	1	0	1	0	0	1	0	1	0	1
3	1	0	0	0	0	1	0	1	0	0	0	0	0
4	1	0	1	1	0	1	0	0	0	1			

-- DataDN Table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	2	1	1	1	4	1	1	3					
2	2	2	1	1	1	2	1	1	1	1	1		
3	2	4	1	1	1	5							
4	2	1	2	1	1	3	1

-- DataCN1 Table

SID	TID	T1	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	5	12	7	2	2	1	0	0	0	0	0	0	0	0
2	5	12	10	2	0	0	0	0	0	0	0	0	0	0
3	5	12	5	2	2	2	1	0	0	0	0	0	0	0
4	5	9	6	2	1	0	0	0	0	0	0	0	0	0

-- Example Calculation of row SID #1 of DataCN1 Table

T1	=	12	Total count of all Binary number of Table DataBN (C1 to C12)
C1	=	7	Total count of correct Binary number of Table DataDN (C1 to C7)
C2	=	2	Total count of Binary number => 2 of table DataDN (C4 & C7)
C3	=	2	Total count of Binary number => 3 of table DataDN (C4 & C7)
C4	=	1	Total count of Binary number => 4 of table DataDN (C4)
C5	=	0	Total count of Binary number => 5 of table DataDN
C6	=	0	Total count of Binary number => 6 of table DataDN
C7	=	0	Total count of Binary number => 7 of table DataDN
C8	=	0	Total count of Binary number => 8 of table DataDN
C9	=	0	Total count of Binary number => 9 of table DataDN
C10	=	0	Total count of Binary number => 10 of table DataDN
C11	=	0	Total count of Binary number => 11 of table DataDN
C12	=	0	Total count of Binary number => 12 of table DataDN

				
Re: Count group data [message #653536 is a reply to message #653533] Sat, 09 July 2016 19:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Sorry, but either your desired results and explanation of calculation do not match your sample data or I do not understand.

For SID 1 in DataDN, you have:

SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	2	1	1	1	4	1	1	3					

For SID 1 in DataCN1 you say you want:

SID	TID	T1	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	5	12	7	2	2	1	0	0	0	0	0	0	0	0

You say to calculate like so:

C2	=	2	Total count of Binary number => 2 of table DataDN (C4 & C7)
C3	=	2	Total count of Binary number => 3 of table DataDN (C4 & C7)
C4	=	1	Total count of Binary number => 4 of table DataDN (C4)

It looks to me like it should be:

SID	TID	T1	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	5	12	7	0	1	1	0	0	0	0	0	0	0	0

C2	=	0	Total count of Binary number => 2 of table DataDN 
C3	=	1	Total count of Binary number => 3 of table DataDN (C4)
C4	=	1	Total count of Binary number => 4 of table DataDN (C7)

Please correct or explain.
Re: Count group data [message #653540 is a reply to message #653536] Sun, 10 July 2016 02:52 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara, Sorry for the confusion of my explanation. Please see below the updates.



-- Example Calculation of row SID #1 of DataCN1 Table
			
T1	=	12	Total count of all Binary number of Table DataBN (C1 to C12)
C1	=	7	Total count of correct Binary number of Table DataDN (C1 to C7)
C2	=	2	Count repeated  binary number at least 2 times of "1" and "0" of table DataDN. C4 = 4 of "0" and C7 = 3 of "1" these two columns have at least 2 repeated binary numbers.
C3	=	2	Count repeated  binary number at least 3 times of "1" and "0" of table DataDN. C4 = 4 of "0" and C7 = 3 of "1"  these two columns have at least 3 repeated binary numbers.
C4	=	1	Count repeated  binary number at least 4 times of "1" and "0" of table DataDN. C4 = 4 of "0" and only this colum has at least 4 repeated binary numbers.
C5	=	0	Count repeated  binary number at least 5 times of "1" and "0" of table DataDN. There is no column has at least 5 repeated binary numbers
C6	=	0	Count repeated  binary number at least 6 times of "1" and "0" of table DataDN. There is no column has at least 6 repeated binary numbers
C7	=	0	Count repeated  binary number at least 7 times of "1" and "0" of table DataDN. There is no column has at least 7 repeated binary numbers
C8	=	0	Count repeated  binary number at least 8 times of "1" and "0" of table DataDN. There is no column has at least 8 repeated binary numbers
C9	=	0	Count repeated  binary number at least 9 times of "1" and "0" of table DataDN. There is no column has at least 9 repeated binary numbers
C10	=	0	Count repeated  binary number at least 10 times of "1" and "0" of table DataDN. There is no column has at least 10 repeated binary numbers
C11	=	0	Count repeated  binary number at least 11 times of "1" and "0" of table DataDN. There is no column has at least 11 repeated binary numbers
C12	=	0	Count repeated  binary number at least 12 times of "1" and "0" of table DataDN. There is no column has at least 12 repeated binary numbers





Re: Count group data [message #653546 is a reply to message #653540] Sun, 10 July 2016 15:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You seem to have renamed your tca and tcc columns to t1 and c1, so I renamed those. It gets confusing and difficult to compare things when you keep changing column names. For the rest of the calculations, I just changed the decode statements to case statements, so that I could use >= instead of just comparing an exact number. You should begin to see the pattern of how to do these things. You can run the individual sub-queries to see what they return. In the future, you should be able to make such changes yourself or at least post some attempt and the results you got.


SCOTT@orcl_12.1.0.2.0> -- test data:
SCOTT@orcl_12.1.0.2.0> select * from databn order by sid
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0
         4          1          0          1          1          0          1          0          0          0          1

4 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datadn order by sid
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          1          1          1          4          1          1          3
         2          2          2          1          1          1          2          1          1          1          1          1
         3          2          4          1          1          1          5
         4          2          1          2          1          1          3          1

4 rows selected.

SCOTT@orcl_12.1.0.2.0> -- select statement that can be used to create table or view:
SCOTT@orcl_12.1.0.2.0> select b.sid, 5 tid, b.t1, d.c1,
  2  	    d.c2,d.c3,d.c4,d.c5,d.c6,d.c7,d.c8,d.c9,d.c10,d.c11,d.c12
  3  from   (select sid, sum(nvl2(val,1,0)) t1
  4  	     from   (select sid, col, val
  5  		     from   databn
  6  		     unpivot include nulls (val for col in
  7  		       (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)))
  8  	     group  by sid) b,
  9  	    (select sid, sum(nvl2(val,1,0)) c1,
 10  		    sum(case when val>=2 then 1 else 0 end) c2,
 11  		    sum(case when val>=3 then 1 else 0 end) c3,
 12  		    sum(case when val>=4 then 1 else 0 end) c4,
 13  		    sum(case when val>=5 then 1 else 0 end) c5,
 14  		    sum(case when val>=6 then 1 else 0 end) c6,
 15  		    sum(case when val>=7 then 1 else 0 end) c7,
 16  		    sum(case when val>=8 then 1 else 0 end) c8,
 17  		    sum(case when val>=9 then 1 else 0 end) c9,
 18  		    sum(case when val>=10 then 1 else 0 end) c10,
 19  		    sum(case when val>=11 then 1 else 0 end) c11,
 20  		    sum(case when val>=12 then 1 else 0 end) c12
 21  	     from   (select sid, col, val
 22  		     from   datadn
 23  		     unpivot include nulls (val for col in
 24  			       (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)))
 25  	     group  by sid) d
 26  where  b.sid = d.sid
 27  order  by sid
 28  /

       SID        TID         T1         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          5         12          7          2          2          1          0          0          0          0          0          0          0          0
         2          5         12         10          2          0          0          0          0          0          0          0          0          0          0
         3          5         12          5          2          2          2          1          0          0          0          0          0          0          0
         4          5          9          6          2          1          0          0          0          0          0          0          0          0          0

4 rows selected.
Re: Count group data [message #653669 is a reply to message #653546] Thu, 14 July 2016 01:10 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara,
Yes, I renamed those columns to t1 and c1 as to standardize column name convention. And yes I'm learning SQL L1 now and pick up lots knowledges from your queries. I did make some adjustments as needed based on your suggestions. To keep things moving along with that, please advise me with the following scenario of creating DataGCN table. I have added three more data rows to the DataBN table to cover all the cases. Thank you for your help! Much appreciated.



-- DataBN Table
SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	1	1	0	1	0	0	0	0	1	0	1	1	1
2	1	0	0	1	0	1	0	0	1	0	1	0	1
3	1	0	0	0	0	1	0	1	0	0	0	0	0
4	1	0	1	1	0	1	0	0	0	1			
5	1	1	1	0	0	1	1	1	1	1	1	0	
6	1	1	1	1	1	1	1	1	1	1	1	1	


-- DataDN table

SID	TID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	2	1	1	1	4	1	1	3					
2	2	2	1	1	1	2	1	1	1	1	1		
3	2	4	1	1	1	5							
4	2	1	2	1	1	3	1						
5	2	2	2	6	1								
6	2	11											

-- DataCN1 Table (created from your last select statement)

SID	TID	T1	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	5	12	7	2	2	1	0	0	0	0	0	0	0	0
2	5	12	10	2	0	0	0	0	0	0	0	0	0	0
3	5	12	5	2	2	2	1	0	0	0	0	0	0	0
4	5	9	6	2	1	0	0	0	0	0	0	0	0	0
5	5	8	4	3	1	1	1	1	0	0	0	0	0	0
6	5	11	1	1	1	1	1	1	1	1	1	1	1	0

-- DataCRN table

SID	TID	T1	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	6	5.50	1.1	1.4	1.5	1.6	1.7	1.10						
2	6	2.00	1.1	4.2	1.3									
3	6	1.17	3.1	2.2										
4	6	0.83	3.1	1.2										
5	6	0.50	3.1											
6	6	0.33	2.1											
7	6	0.17	1.1											
8	6	0.17	1.1											
9	6	0.17	1.1											
10	6	0.17	1.1											
11	6	0.17	1.1											
12	6	0.00	0											
 
-- Example Calculation of row SID #1 of DataCRN Table
T1	=	5.5	Average of C1 column of DataCN1 table as (7+10+5+6+4+1)/6
C1	=	1.1	Count repeated each number in C1 column of DataCN1 table. Put the total count number in the front of number in the format as 1.1 (count.number) . In C1 column of DataCN1, there is only one count of number 1
C2	=	1.4	Same as C1 above. There is only one of number 4
C3	=	1.5	Same as C1 above. There is only one of number 5
C4	=	1.6	Same as C1 above. There is only one of number 6
C5	=	1.7	Same as C1 above. There is only one of number 7
C6	=	1.10	Same as C1 above. There is only one of number 10
C7	=		Same as C1 above. If there is a next number
C8	=		Same as C1 above. If there is a next number
C9	=		Same as C1 above. If there is a next number
C10	=		Same as C1 above. If there is a next number
C11	=		Same as C1 above. If there is a next number
C12	=		Same as C1 above. If there is a next number


 



Re: Count group data [message #653704 is a reply to message #653669] Fri, 15 July 2016 02:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Your databn table now has two additional rows that you did not provide insert statements for.

In your datacn1 table, in the row where sid=5, your t1 value is 8, but it looks like it should be 11.

SCOTT@orcl_12.1.0.2.0> select * from databn order by sid
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          1          0          0          0          0          1          0          1          1          1
         2          1          0          0          1          0          1          0          0          1          0          1          0          1
         3          1          0          0          0          0          1          0          1          0          0          0          0          0
         4          1          0          1          1          0          1          0          0          0          1
         5          1          1          1          0          0          1          1          1          1          1          1          0
         6          1          1          1          1          1          1          1          1          1          1          1          1

6 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datadn order by sid
  2  /

       SID        TID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          1          1          1          4          1          1          3
         2          2          2          1          1          1          2          1          1          1          1          1
         3          2          4          1          1          1          5
         4          2          1          2          1          1          3          1
         5          2          2          2          6          1
         6          2         11

6 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from datacn1 order by sid
  2  /

       SID        TID         T1         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          5         12          7          2          2          1          0          0          0          0          0          0          0          0
         2          5         12         10          2          0          0          0          0          0          0          0          0          0          0
         3          5         12          5          2          2          2          1          0          0          0          0          0          0          0
         4          5          9          6          2          1          0          0          0          0          0          0          0          0          0
         5          5         11          4          3          1          1          1          1          0          0          0          0          0          0
         6          5         11          1          1          1          1          1          1          1          1          1          1          1          0

6 rows selected.

SCOTT@orcl_12.1.0.2.0> select s.sid, s.tid, s.t1,
  2  	    e.c1, e.c2, e.c3, e.c4, e.c5, e.c6, e.c7, e.c8, e.c9, e.c10, e.c11, e.c12
  3  from   (select 1 sid, 6 tid, avg(c1) t1 from  datacn1 union all
  4  	     select 2 sid, 6 tid, avg(c2) t1 from  datacn1 union all
  5  	     select 3 sid, 6 tid, avg(c3) t1 from  datacn1 union all
  6  	     select 4 sid, 6 tid, avg(c4) t1 from  datacn1 union all
  7  	     select 5 sid, 6 tid, avg(c5) t1 from  datacn1 union all
  8  	     select 6 sid, 6 tid, avg(c6) t1 from  datacn1 union all
  9  	     select 7 sid, 6 tid, avg(c7) t1 from  datacn1 union all
 10  	     select 8 sid, 6 tid, avg(c8) t1 from  datacn1 union all
 11  	     select 9 sid, 6 tid, avg(c9) t1 from  datacn1 union all
 12  	     select 10 sid, 6 tid, avg(c10) t1 from  datacn1 union all
 13  	     select 11 sid, 6 tid, avg(c11) t1 from  datacn1 union all
 14  	     select 12 sid, 6 tid, avg(c12) t1 from  datacn1) s,
 15  	    (select sid,
 16  		    sum (decode (n, 1, c)) c1,
 17  		    sum (decode (n, 2, c)) c2,
 18  		    sum (decode (n, 3, c)) c3,
 19  		    sum (decode (n, 4, c)) c4,
 20  		    sum (decode (n, 5, c)) c5,
 21  		    sum (decode (n, 6, c)) c6,
 22  		    sum (decode (n, 7, c)) c7,
 23  		    sum (decode (n, 8, c)) c8,
 24  		    sum (decode (n, 9, c)) c9,
 25  		    sum (decode (n, 10, c)) c10,
 26  		    sum (decode (n, 11, c)) c11,
 27  		    sum (decode (n, 12, c)) c12
 28  	     from   (select 1 sid, count(*)||'.'|| c1 c, dense_rank () over (order by c1) n from datacn1 where c1 > 0 group by c1 union all
 29  		     select 2 sid, count(*)||'.'|| c2 c, dense_rank () over (order by c2) n from datacn1 where c2 > 0 group by c2 union all
 30  		     select 3 sid, count(*)||'.'|| c3 c, dense_rank () over (order by c3) n from datacn1 where c3 > 0 group by c3 union all
 31  		     select 4 sid, count(*)||'.'|| c4 c, dense_rank () over (order by c4) n from datacn1 where c4 > 0 group by c4 union all
 32  		     select 5 sid, count(*)||'.'|| c5 c, dense_rank () over (order by c5) n from datacn1 where c5 > 0group by c5 union all
 33  		     select 6 sid, count(*)||'.'|| c6 c, dense_rank () over (order by c6) n from datacn1 where c6 > 0 group by c6 union all
 34  		     select 7 sid, count(*)||'.'|| c7 c, dense_rank () over (order by c7) n from datacn1 where c7 > 0 group by c7 union all
 35  		     select 8 sid, count(*)||'.'|| c8 c, dense_rank () over (order by c8) n from datacn1 where c8 > 0 group by c8 union all
 36  		     select 9 sid, count(*)||'.'|| c9 c, dense_rank () over (order by c9) n from datacn1 where c9 > 0 group by c9 union all
 37  		     select 10 sid, count(*)||'.'|| c10 c, dense_rank () over (order by c10) n from datacn1 where c10 > 0 group by c10 union all
 38  		     select 11 sid, count(*)||'.'|| c11 c, dense_rank () over (order by c11) n from datacn1 where c11 > 0 group by c11 union all
 39  		     select 12 sid, count(*)||'.'|| c12 c, dense_rank () over (order by c12) n from datacn1 where c12 > 0 group by c12)
 40  	     group  by sid) e
 41  where  s.sid = e.sid(+)
 42  order  by sid
 43  /

       SID        TID         T1         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          6        5.5        1.1        1.4        1.5        1.6        1.7        1.1
         2          6          2        1.1        4.2        1.3
         3          6 1.16666667        3.1        2.2
         4          6 .833333333        3.1        1.2
         5          6         .5        3.1
         6          6 .333333333        2.1
         7          6 .166666667        1.1
         8          6 .166666667        1.1
         9          6 .166666667        1.1
        10          6 .166666667        1.1
        11          6 .166666667        1.1
        12          6          0

12 rows selected.

[Updated on: Fri, 15 July 2016 03:35]

Report message to a moderator

Re: Count group data [message #653952 is a reply to message #653704] Sat, 23 July 2016 13:36 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara, Thanks much again and really appreciate your help. I tested and verified the result as expected in the DEV Environment excepting a small confusing of when reading the decimal number.

-- For example in our DEV Env, if the column "C1" of datacn1 table has number "3" and "30" such as below
C1
----
1
2
3
7
30

-- The result from the above select statement of column "C1" is
1.1	1.2	1.3 	1.7 	1.3

So is there an easy way to fix the last number "1.3" to show as "1.30" as it supported to be.

Thanks again!


Re: Count group data [message #653955 is a reply to message #653952] Sat, 23 July 2016 15:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
It looks like changing sum to max fixes it. That prevents the concatenated numbers and periods from being implicitly converted from character to number. So, the value in column c6 for the sid 1 is now 1.10 instead of 1.1

SCOTT@orcl_12.1.0.2.0> select s.sid, s.tid, s.t1,
  2  	    e.c1, e.c2, e.c3, e.c4, e.c5, e.c6, e.c7, e.c8, e.c9, e.c10, e.c11, e.c12
  3  from   (select 1 sid, 6 tid, avg(c1) t1 from  datacn1 union all
  4  	     select 2 sid, 6 tid, avg(c2) t1 from  datacn1 union all
  5  	     select 3 sid, 6 tid, avg(c3) t1 from  datacn1 union all
  6  	     select 4 sid, 6 tid, avg(c4) t1 from  datacn1 union all
  7  	     select 5 sid, 6 tid, avg(c5) t1 from  datacn1 union all
  8  	     select 6 sid, 6 tid, avg(c6) t1 from  datacn1 union all
  9  	     select 7 sid, 6 tid, avg(c7) t1 from  datacn1 union all
 10  	     select 8 sid, 6 tid, avg(c8) t1 from  datacn1 union all
 11  	     select 9 sid, 6 tid, avg(c9) t1 from  datacn1 union all
 12  	     select 10 sid, 6 tid, avg(c10) t1 from  datacn1 union all
 13  	     select 11 sid, 6 tid, avg(c11) t1 from  datacn1 union all
 14  	     select 12 sid, 6 tid, avg(c12) t1 from  datacn1) s,
 15  	    (select sid,
 16  		    max (decode (n, 1, c)) c1,
 17  		    max (decode (n, 2, c)) c2,
 18  		    max (decode (n, 3, c)) c3,
 19  		    max (decode (n, 4, c)) c4,
 20  		    max (decode (n, 5, c)) c5,
 21  		    max (decode (n, 6, c)) c6,
 22  		    max (decode (n, 7, c)) c7,
 23  		    max (decode (n, 8, c)) c8,
 24  		    max (decode (n, 9, c)) c9,
 25  		    max (decode (n, 10, c)) c10,
 26  		    max (decode (n, 11, c)) c11,
 27  		    max (decode (n, 12, c)) c12
 28  	     from   (select 1 sid, count(*)||'.'|| c1 c, dense_rank () over (order by c1) n from datacn1 where c1 > 0 group by c1 union all
 29  		     select 2 sid, count(*)||'.'|| c2 c, dense_rank () over (order by c2) n from datacn1 where c2 > 0 group by c2 union all
 30  		     select 3 sid, count(*)||'.'|| c3 c, dense_rank () over (order by c3) n from datacn1 where c3 > 0 group by c3 union all
 31  		     select 4 sid, count(*)||'.'|| c4 c, dense_rank () over (order by c4) n from datacn1 where c4 > 0 group by c4 union all
 32  		     select 5 sid, count(*)||'.'|| c5 c, dense_rank () over (order by c5) n from datacn1 where c5 > 0group by c5 union all
 33  		     select 6 sid, count(*)||'.'|| c6 c, dense_rank () over (order by c6) n from datacn1 where c6 > 0 group by c6 union all
 34  		     select 7 sid, count(*)||'.'|| c7 c, dense_rank () over (order by c7) n from datacn1 where c7 > 0 group by c7 union all
 35  		     select 8 sid, count(*)||'.'|| c8 c, dense_rank () over (order by c8) n from datacn1 where c8 > 0 group by c8 union all
 36  		     select 9 sid, count(*)||'.'|| c9 c, dense_rank () over (order by c9) n from datacn1 where c9 > 0 group by c9 union all
 37  		     select 10 sid, count(*)||'.'|| c10 c, dense_rank () over (order by c10) n from datacn1 where c10 > 0 group by c10 union all
 38  		     select 11 sid, count(*)||'.'|| c11 c, dense_rank () over (order by c11) n from datacn1 where c11 > 0 group by c11 union all
 39  		     select 12 sid, count(*)||'.'|| c12 c, dense_rank () over (order by c12) n from datacn1 where c12 > 0 group by c12)
 40  	     group  by sid) e
 41  where  s.sid = e.sid(+)
 42  order  by sid
 43  /

       SID        TID         T1 C1    C2    C3    C4    C5    C6    C7    C8    C9    C10   C11   C12
---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
         1          6        5.5 1.1   1.4   1.5   1.6   1.7   1.10
         2          6          2 1.1   4.2   1.3
         3          6 1.16666667 3.1   2.2
         4          6 .833333333 3.1   1.2
         5          6         .5 3.1
         6          6 .333333333 2.1
         7          6 .166666667 1.1
         8          6 .166666667 1.1
         9          6 .166666667 1.1
        10          6 .166666667 1.1
        11          6 .166666667 1.1
        12          6          0

12 rows selected.

[Updated on: Sun, 24 July 2016 03:32]

Report message to a moderator

Re: Count group data [message #654490 is a reply to message #653955] Wed, 03 August 2016 23:44 Go to previous message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara, thank you so much and I really appreciate your helps. Just got back from the travel. Yes, looks like the max function corrected the problem. Thank you again!
Previous Topic: Make use of UNUSABLE_INDEX by uisnh HInt
Next Topic: Regular expression search
Goto Forum:
  


Current Time: Mon Oct 25 20:44:16 CDT 2021