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  |
 |
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  |
John Watson
Messages: 8930
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 |
 |
Barbara Boehmer
Messages: 9090
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 |
|
Barbara Boehmer
Messages: 9090
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 #653346 is a reply to message #653345] |
Sun, 03 July 2016 01:30 |
|
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 #653350 is a reply to message #653346] |
Sun, 03 July 2016 14:34 |
|
Barbara Boehmer
Messages: 9090
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 |
|
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 |
|
Barbara Boehmer
Messages: 9090
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 #653481 is a reply to message #653480] |
Wed, 06 July 2016 22:46 |
|
Barbara Boehmer
Messages: 9090
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 #653546 is a reply to message #653540] |
Sun, 10 July 2016 15:37 |
|
Barbara Boehmer
Messages: 9090
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 #653704 is a reply to message #653669] |
Fri, 15 July 2016 02:44 |
|
Barbara Boehmer
Messages: 9090
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 #653955 is a reply to message #653952] |
Sat, 23 July 2016 15:24 |
|
Barbara Boehmer
Messages: 9090
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 |
|
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!
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 17:35:24 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
