Home » SQL & PL/SQL » SQL & PL/SQL » How to Get Such resultset by count(),rollup .etc....?
How to Get Such resultset by count(),rollup .etc....? [message #263818] Fri, 31 August 2007 02:04 Go to next message
slightleaves
Messages: 15
Registered: August 2007
Location: china
Junior Member

I've got a table as follow

------------------------
id c1 c2
1 1 2
2 2 3
3 3 4
4 1 3
5 2 3
------------------------

I want to calculate the number of '1' in column c1 and
the number of '3' in column c2
the sql I use is as follow
-----------------------------------------
select id,
c1,
c2
from table t
union
select '-1' id,
count(decode(tt.c1,1,'x',null)) c1,
count(decode(tt.c2,3,'x',null)) c2
from table tt
------------------------------------------
and produce the resultset like follows

------------------------
id c1 c2
-1 2 1
1 1 2
2 2 3
3 3 4
4 1 3
5 2 3
------------------------

So,I wonder is there any simple sql which doesn't use 'UNION' clause and can produce the same resultset

**************************************
Re: How to Get Such resultset by count(),rollup .etc....? [message #263850 is a reply to message #263818] Fri, 31 August 2007 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you'd:
- Followed OraFAQ Forum Guide, including "How to format your post?" section (Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button)
- Posted your Oracle version (4 decimals)
- Posted a test case

Maybe I'd showed you how to do it.

Regards
Michel
Re: How to Get Such resultset by count(),rollup .etc....? [message #264091 is a reply to message #263850] Fri, 31 August 2007 13:51 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Michel Cadot wrote on Fri, 31 August 2007 01:15
Maybe I'd showed you how to do it.

Always such a tease Wink
Re: How to Get Such resultset by count(),rollup .etc....? [message #264150 is a reply to message #263850] Sat, 01 September 2007 03:16 Go to previous messageGo to next message
slightleaves
Messages: 15
Registered: August 2007
Location: china
Junior Member

Oracle Version: Oracle9i Enterprise Edition Release 9.2.0.4.0
OS : Windwos XP

Thank you for Regards Michel's advice,now I'll use the test case as follow
create table TEST3
(
  ID NUMBER(4),
  C1 NUMBER(4),
  C2 NUMBER(4)
);

insert into TEST3 (ID, C1, C2)
values (1, 1, 2);
insert into TEST3 (ID, C1, C2)
values (2, 2, 4);
insert into TEST3 (ID, C1, C2)
values (3, 3, 3);
insert into TEST3 (ID, C1, C2)
values (4, 1, 4);
insert into TEST3 (ID, C1, C2)
values (5, 2, 4);
commit;
SQL> select * from test3;

        ID         C1         C2
---------- ---------- ----------
         1          1          2
         2          2          4
         3          3          3
         4          1          4
         5          2          4
5 rows selected


And,I want to calculate the number of '1' in column c1 and
the number of '4' in column c2
the sql I use is as follow
SQL> SELECT ID,
  2         C1,
  3         C2
  4  FROM   TEST3 T
  5  UNION 
  6  SELECT - 1 ID,
  7         COUNT(DECODE(TT.C1,1,'x',
  8                            NULL)) C1,
  9         COUNT(DECODE(TT.C2,4,'x',
 10                            NULL)) C2
 11  FROM   TEST3 TT;

        ID         C1         C2
---------- ---------- ----------
        -1          2          3
         1          1          2
         2          2          4
         3          3          3
         4          1          4
         5          2          4
6 rows selected

So,anyone can help me to find another easier way to get the same resultset ,which doesn't use 'UNION' clause and only access the table for one time.

[Updated on: Sat, 01 September 2007 03:54]

Report message to a moderator

Re: How to Get Such resultset by count(),rollup .etc....? [message #264190 is a reply to message #264150] Sat, 01 September 2007 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by id;
        ID         C1         C2
---------- ---------- ----------
         1          1          2
         2          2          3
         3          3          4
         4          1          3
         5          2          3

5 rows selected.

SQL> select decode(grouping(id),0,id,-1) id,
  2         decode(grouping(id),0,c1,sum(decode(c1,1,1))) c1,
  3         decode(grouping(id),0,c2,sum(decode(c2,3,1))) c2
  4  from t
  5  group by rollup((id, c1, c2))
  6  order by 1
  7  /
        ID         C1         C2
---------- ---------- ----------
        -1          2          3
         1          1          2
         2          2          3
         3          3          4
         4          1          3
         5          2          3

6 rows selected.

Regards
Michel
Re: How to Get Such resultset by count(),rollup .etc....? [message #264193 is a reply to message #264190] Sat, 01 September 2007 16:42 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Quote:
Posted a test case

And then you didn't even use the INSERT statements? C2 doesn't match, and the original post was looking for the number of 4's...but we get the idea. I'd give you an A minus Wink
Re: How to Get Such resultset by count(),rollup .etc....? [message #264203 is a reply to message #264193] Sun, 02 September 2007 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cmerry wrote on Sat, 01 September 2007 23:42
Quote:
Posted a test case

And then you didn't even use the INSERT statements? C2 doesn't match, and the original post was looking for the number of 4's...but we get the idea. I'd give you an A minus Wink

Just to make him work a little bit and not just copy and paste the result, if it is a homework. Wink

Regards
Michel

Re: How to Get Such resultset by count(),rollup .etc....? [message #264212 is a reply to message #263818] Sun, 02 September 2007 01:21 Go to previous messageGo to next message
slightleaves
Messages: 15
Registered: August 2007
Location: china
Junior Member

Actually,it's not a homework,and it's the problem I met in the work,and I just simplify the problem . Now, there's another problem. Test case is as follow
create table TEST3
(
  ID NUMBER(4),
  C1 NUMBER(4),
  C2 NUMBER(4),
  C3 VARCHAR2(5),
  C4 NUMBER(4),
  C5 VARCHAR2(5)
);

insert into TEST3 (ID, C1, C2, C3, C4, C5)
values (1, 1, 2, null, 32, null);
insert into TEST3 (ID, C1, C2, C3, C4, C5)
values (2, 2, 4, null, 4, null);
insert into TEST3 (ID, C1, C2, C3, C4, C5)
values (3, 3, 3, '3', null, 'A');
insert into TEST3 (ID, C1, C2, C3, C4, C5)
values (4, 1, 4, '4', null, 'BB');
insert into TEST3 (ID, C1, C2, C3, C4, C5)
values (5, 2, 4, 'AA', 3, 'CC');
commit;
SQL> select * from test3;
 
   ID    C1    C2 C3       C4 C5
----- ----- ----- ----- ----- -----
    1     1     2          32 
    2     2     4           4 
    3     3     3 3           A
    4     1     4 4           BB
    5     2     4 AA        3 CC
5 rows selected


there are totally 5 columns in table,I only want to calculate column 'c1',and column 'c2'.I use the sql as follow
SQL>  SELECT DECODE(GROUPING(ID), 0, ID, -1) ID,
  2          DECODE(GROUPING(ID), 0, C1, SUM(DECODE(C1, 1, 1))) C1,
  3          DECODE(GROUPING(ID), 0, C2, SUM(DECODE(C2, 4, 1))) C2,
  4          DECODE(GROUPING(ID), 0, C3) C3,
  5          DECODE(GROUPING(ID), 0, C4) C4,
  6          DECODE(GROUPING(ID), 0, C5) C5
  7     FROM TEST3 
  8    GROUP BY ROLLUP((ID, C1, C2, C3, C4, C5))
  9    ORDER BY 1;

        ID         C1         C2 C3            C4 C5
---------- ---------- ---------- ----- ---------- -----
        -1          2          3
         1          1          2               32
         2          2          4                4
         3          3          3 3                A
         4          1          4 4                BB
         5          2          4 AA             3 CC
6 rows selected


So,if there's over 50 or even more columns in the table,does that mean I have to put 'c1','c2','c3'...'c50',in the rollup clause,is there some other way? And,Michel Cadot,would you please tell me why you need put a pair of in-line bracket in the rollup clause,thanks!
Re: How to Get Such resultset by count(),rollup .etc....? [message #264267 is a reply to message #264212] Sun, 02 September 2007 10:21 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Actually,it's not a homework,and it's the problem I met in the work,and I just simplify the problem

And we thank you for this.
I thought this is the case, this is why I didn't move it to Homework forum.

Quote:
So,if there's over 50 or even more columns in the table,does that mean I have to put 'c1','c2','c3'...'c50',in the rollup clause,is there some other way?

As the formula for each column is distinct (-1, count 1, count 3, ...) you have to express it for each column.

Quote:
you please tell me why you need put a pair of in-line bracket in the rollup clause

I should say: try with only one pair of bracket. Wink
Oracle returns the result of the rollup for each successive list of columns. For instance, "rollup(l1,l2,l3)" returns result grouping by l3, (l2, l3) and (l1, l2, l3).
Here you want only one rollup, the one for all columns.

Regards
Michel

Previous Topic: Need help on hirerchy grouping
Next Topic: pl/sql procedure
Goto Forum:
  


Current Time: Tue Dec 06 04:23:57 CST 2016

Total time taken to generate the page: 0.04889 seconds