Home » SQL & PL/SQL » SQL & PL/SQL » Rollup (10g,win)
Rollup [message #427950] Mon, 26 October 2009 09:22 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT   SYS, package_id, package_seq, developer,
            object_type
         || DECODE (dependency, '0', NULL, '(' || dependency || ')')
                                                                  object_type,
         object_name, object_location, distinct_objname, rel_code, release,
         rsi, deploy_end_time, rank_deploy, migration_time, deployment_time,
         0 s, 0 t, 0 o
    FROM TEST
UNION
SELECT   SYS, NULL package_id, NULL package_seq, NULL developer, object_type,
         NULL object_name, NULL object_location, NULL distinct_objname,
         NULL rel_code, NULL release, NULL rsi, NULL deploy_end_time,
         NULL rank_deploy, migration_time, deployment_time, s, t, o
    FROM (SELECT   SYS,
                      object_type
                   || DECODE (dependency,
                              '0', NULL,
                              '(' || dependency || ')'
                             ) object_type,
                   distinct_objname, SUM (migration_time) migration_time,
                   SUM (deployment_time) deployment_time, GROUPING (SYS) AS s,
                   GROUPING (   object_type
                             || DECODE (dependency,
                                        '0', NULL,
                                        '(' || dependency || ')'
                                       )
                            ) AS t,
                   GROUPING (distinct_objname) AS o
              FROM TEST
          GROUP BY ROLLUP (SYS,
                              object_type
                           || DECODE (dependency,
                                      '0', NULL,
                                      '(' || dependency || ')'
                                     ),
                           distinct_objname))
   WHERE s = 1 OR t = 1 OR o = 1
ORDER BY SYS, object_type, object_name


Is there any better way to write this??
As you can see I want all rows + group by (over only 3 rows)

[MERGED by LF; duplicate message deleted]

[Updated on: Mon, 26 October 2009 09:44] by Moderator

Report message to a moderator

Re: Rollup [message #427963 is a reply to message #427950] Mon, 26 October 2009 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you explain what you want to do and post a test case instead on relying on us to reverse engineer your code?
For myself, I will not have a look at it.

Regards
Michel
Re: Rollup [message #427979 is a reply to message #427963] Mon, 26 October 2009 11:11 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> create table logic
  2  (
  3  a varchar2(10),
  4  b number,
  5  c date,
  6  d number,
  7  e number
  8  );

Table created.

SQL> insert into logic values('BO',1,SYSDATE,10,20);

1 row created.

SQL>  insert into logic values('BO',2,SYSDATE,10,20);

1 row created.

SQL>  insert into logic values('BO',3,SYSDATE-1,20,30);

1 row created.

SQL>  insert into logic values('FO',4,SYSDATE-1,20,30);

1 row created.

SQL>  insert into logic values('FO',5,SYSDATE-2,30,40);

1 row created.

SQL> commit;

Commit complete.

  1  select a,b,c,d,e
  2  from logic
  3  union
  4  select a, null b,c,null d,e
  5  from
  6  (
  7  select a,c,sum(e) e
  8  from logic
  9  group by rollup(a,c)
 10  )
 11* order by 1,2,3,4,5
SQL> /

A                   B C                  D          E
---------- ---------- --------- ---------- ----------
BO                  1 26-OCT-09         10         20
BO                  2 26-OCT-09         10         20
BO                  3 25-OCT-09         20         30
BO                    25-OCT-09                    30
BO                    26-OCT-09                    20
BO                    26-OCT-09                    20
BO                                                 70
FO                  4 25-OCT-09         20         30
FO                  5 24-OCT-09         30         40
FO                    24-OCT-09                    40
FO                    25-OCT-09                    30

A                   B C                  D          E
---------- ---------- --------- ---------- ----------
FO                                                 70
                                                  140

13 rows selected.

I want to see all data but group only over 2 columns
Re: Rollup [message #427980 is a reply to message #427979] Mon, 26 October 2009 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to see all data but group only over 2 columns

What does this mean? Is your output what you want or not?

Regards
Michel

Re: Rollup [message #427981 is a reply to message #427980] Mon, 26 October 2009 11:40 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Yes I want the same output
But still looking for some simpler/efficient/shorter method

IS it possible using GROUPING SETS?
Re: Rollup [message #427983 is a reply to message #427981] Mon, 26 October 2009 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You said: "I want to see all data but group only over 2 columns" but the last 2 lines of your output clearly show grouping over 3 or 4 columns.

Regards
Michel

[Updated on: Mon, 26 October 2009 11:43]

Report message to a moderator

Re: Rollup [message #427984 is a reply to message #427983] Mon, 26 October 2009 11:46 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I am just displaying all columns the data is grouped over 2 columns.

select all data
union
select a,c,sum() -- all rest fields null
grouped over rollup(a,c)
SQL>  select a,b,c,d,e
  2   from logic
  3   union
  4   select a, null b,c,null d,e
  5   from
  6   (
  7   select a,c,sum(e) e
  8   from logic
  9   group by rollup(a,c)
 10   )
 11  order by 1,3;

A                   B C                  D          E
---------- ---------- --------- ---------- ----------
BO                  3 25-OCT-09         20         30
BO                    25-OCT-09                    30
BO                  1 26-OCT-09         10         20
BO                    26-OCT-09                    20
BO                  2 26-OCT-09         10         20
BO                    26-OCT-09                    20
BO                                                 70
FO                  5 24-OCT-09         30         40
FO                    24-OCT-09                    40
FO                  4 25-OCT-09         20         30
FO                    25-OCT-09                    30

A                   B C                  D          E
---------- ---------- --------- ---------- ----------
FO                                                 70
                                                  140

[Updated on: Mon, 26 October 2009 11:48]

Report message to a moderator

Re: Rollup [message #427985 is a reply to message #427984] Mon, 26 October 2009 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems to me that:
A                   B C                  D          E
---------- ---------- --------- ---------- ----------
FO                                                 70
                                                  140

Are grouped over 3 or 4 columns otherwise why C and A are NULL?

Regards
Michel
Re: Rollup [message #427986 is a reply to message #427985] Mon, 26 October 2009 11:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> select a,b,c,d,e
  2  from logic;

A                   B C                  D          E
---------- ---------- --------- ---------- ----------
BO                  1 26-OCT-09         10         20
BO                  2 26-OCT-09         10         20
BO                  3 25-OCT-09         20         30
FO                  4 25-OCT-09         20         30
FO                  5 24-OCT-09         30         40

SQL>  select a, null b,c,null d,e
  2   from
  3   (
  4   select a,c,sum(e) e
  5   from logic
  6   group by rollup(a,c)
  7   );

A          B C         D          E
---------- - --------- - ----------
BO           25-OCT-09           30
BO           26-OCT-09           20
BO           26-OCT-09           20
BO                               70
FO           24-OCT-09           40
FO           25-OCT-09           30
FO                               70
                                140

8 rows selected.


combine these 2 using union ,I think today you overworked Wink
Re: Rollup [message #427987 is a reply to message #427986] Mon, 26 October 2009 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I see the result, I see the queries, both don't match with your requirements in words. That's all. Make them match and I will start searching.

Regards
Michel
Re: Rollup [message #427992 is a reply to message #427987] Mon, 26 October 2009 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, here's the query: you want to have all rows, it is the same as to group on all columns and grouping with a roll up on a and c:
SQL> select a, b, c, d, sum(e) e
  2  from logic
  3  group by grouping sets((a,b,c,d,e), rollup(a,c))
  4  order by 1,2,3,4,5
  5  /
A                   B C                    D          E
---------- ---------- ----------- ---------- ----------
BO                  1 26-OCT-2009         10         20
BO                  2 26-OCT-2009         10         20
BO                  3 25-OCT-2009         20         30
BO                    25-OCT-2009                    30
BO                    26-OCT-2009                    20
BO                    26-OCT-2009                    20
BO                                                   70
FO                  4 25-OCT-2009         20         30
FO                  5 24-OCT-2009         30         40
FO                    24-OCT-2009                    40
FO                    25-OCT-2009                    30
FO                                                   70
                                                    140

13 rows selected.

Regards
Michel
Re: Rollup [message #428104 is a reply to message #427992] Tue, 27 October 2009 04:39 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Great work as always.. Thanks..
Sorry for late reply ..have some doubt still trying to figure it
THink require reading Smile

[Updated on: Tue, 27 October 2009 04:52]

Report message to a moderator

Previous Topic: Close DBLinks
Next Topic: select query giving exception
Goto Forum:
  


Current Time: Thu Sep 29 07:22:05 CDT 2016

Total time taken to generate the page: 0.10018 seconds