OraFAQ Forum: SQL & PL/SQL » grouping logic

Home » SQL & PL/SQL » SQL & PL/SQL » grouping logic (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

grouping logic [message #684218]

Sat, 24 April 2021 12:39

yashi7777
Messages: 42
Registered: March 2016

Member

Requesting help. How to get this done.

create table grp

(key1  varchar2(10),

key2  varchar2(10),

grpfield varchar2(10),

amount number);

 

SET DEFINE OFF;

Insert into GRP

   (KEY1, KEY2, GRPFIELD, AMOUNT)

Values

   ('a', 'b', 'xyz', 400);

Insert into GRP

   (KEY1, KEY2, GRPFIELD, AMOUNT)

Values

   ('a', 'b', 'lm', 300);

Insert into GRP

   (KEY1, KEY2, GRPFIELD, AMOUNT)

Values

   ('a', 'b', 'bo', 100);

Insert into GRP

   (KEY1, KEY2, GRPFIELD, AMOUNT)

Values

   ('a', 'c', 'rr', 200);

Insert into GRP

   (KEY1, KEY2, GRPFIELD, AMOUNT)

Values

   ('a', 'c', 'mh', 220);

Insert into GRP

   (KEY1, KEY2, GRPFIELD, AMOUNT)

Values

   ('d', 'b', 'te', 100);

Insert into GRP

   (KEY1, KEY2, GRPFIELD, AMOUNT)

Values

   ('d', 'b', 'xyz', 200);

COMMIT;

expected output:

---------------------

a b xyz 800

a c rr 200

a c mh 220

d b xyz 300

For a composite key (key1 and key2) , if there is grpfield='xyz' then rollup amount else do not rollup

Report message to a moderator

Re: grouping logic [message #684219 is a reply to message #684218]

Sat, 24 April 2021 13:16

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

Rollup what? I don't see how you get the expected output from the given input. Please explain the logic.

Here's an easier to read form of what you've provided, plus the output at that point:

SQL> create table grp
  2  (key1  varchar2(10),
  3  key2  varchar2(10),
  4  grpfield varchar2(10),
  5  amount number);

Table created.

SQL>
SQL> SET DEFINE OFF;
SQL>
SQL> Insert into GRP (KEY1, KEY2, GRPFIELD, AMOUNT)
  2  Values ('a', 'b', 'xyz', 400);

1 row created.

SQL>
SQL> Insert into GRP (KEY1, KEY2, GRPFIELD, AMOUNT)
  2  Values ('a', 'b', 'lm', 300);

1 row created.

SQL>
SQL> Insert into GRP (KEY1, KEY2, GRPFIELD, AMOUNT)
  2  Values ('a', 'b', 'bo', 100);

1 row created.

SQL>
SQL> Insert into GRP (KEY1, KEY2, GRPFIELD, AMOUNT)
  2  Values ('a', 'c', 'rr', 200);

1 row created.
SQL>
SQL> Insert into GRP (KEY1, KEY2, GRPFIELD, AMOUNT)
  2  Values ('a', 'c', 'mh', 220);

1 row created.

SQL>
SQL> insert into grp (KEY1, KEY2, GRPFIELD, AMOUNT)
  2  Values ('d', 'b', 'te', 100);

1 row created.

SQL>
SQL> Insert into GRP (KEY1, KEY2, GRPFIELD, AMOUNT)
  2  Values ('d', 'b', 'xyz', 200);

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> select * from grp;

KEY1       KEY2       GRPFIELD       AMOUNT
---------- ---------- ---------- ----------
a          b          xyz               400
a          b          lm                300
a          b          bo                100
a          c          rr                200
a          c          mh                220
d          b          te                100
d          b          xyz               200

7 rows selected.

SQL> --
SQL> drop table grp purge;

Table dropped.

Report message to a moderator

Re: grouping logic [message #684221 is a reply to message #684219]

Sat, 24 April 2021 16:34

yashi7777
Messages: 42
Registered: March 2016

Member

Thanks. let me explain the logic of the output.

For the key1 and key2 if the column grpfield has xyz then i rollup the amount, else not

KEY1 KEY2 GRPFIELD AMOUNT
---------- ---------- ---------- ----------
a b xyz 400
a b lm 300
a b bo 100
-------------------------------------------
a c rr 200
a c mh 220
--------------------------------------------
d b te 100
d b xyz 200

my output for 3 above scenario based on my logic

a b xyz 800
a c rr 200
a c mh 220
d b xyz 300

Report message to a moderator

Re: grouping logic [message #684222 is a reply to message #684221]

Sun, 25 April 2021 00:41

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 25 January 2021 16:02

Michel Cadot wrote on Sat, 23 January 2021 21:13

Michel Cadot wrote on Thu, 31 March 2016 10:05

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Your requirements are not clear.

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.
In these data, post several examples which cover all the cases you want to handle and can have.
Michel Cadot wrote on Thu, 31 March 2016 19:56
...
Explain each output column for each row.
...
BlackSwan wrote on Fri, 16 August 2019 22:21
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Michel Cadot wrote on Sat, 17 August 2019 08:16

Also always post your Oracle version, with 4 decimals, as solution depends on it.
...

Query v$version.

[Updated on: Sun, 25 April 2021 00:41]

Report message to a moderator

Re: grouping logic [message #684223 is a reply to message #684221]

Sun, 25 April 2021 03:18

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> with
...
 12  order by 1, 2, 3
 13  /
KEY1       KEY2       GRPFIELD       AMOUNT
---------- ---------- ---------- ----------
a          b          xyz               800
a          c          mh                220
a          c          rr                200
d          b          xyz               300

4 rows selected.

[Updated on: Sun, 25 April 2021 05:04]

Report message to a moderator

Re: grouping logic [message #684224 is a reply to message #684218]

Sun, 25 April 2021 05:04

Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA

Senior Member

with t as (
           select  key1,
                   key2,
                   case max(case grpfield when 'xyz' then 'xyz' end) over(partition by key1,key2)
                     when 'xyz' then 0
                     else rownum
                   end grpnum,
                   grpfield,
                   amount
             from  grp
          )
select  key1,
        key2,
        case grpnum
          when 0 then 'xyz'
          else max(grpfield)
        end grpfield,
        sum(amount) amount
  from  t
  group by key1,
           key2,
           grpnum
  order by key1,
           key2,
           grpnum
/

KEY1       KEY2       GRPFIELD       AMOUNT
---------- ---------- ---------- ----------
a          b          xyz               800
a          c          rr                200
a          c          mh                220
d          b          xyz               300

SQL>

SY.

Report message to a moderator

Re: grouping logic [message #684242 is a reply to message #684224]

Mon, 26 April 2021 20:01

yashi7777
Messages: 42
Registered: March 2016

Member

Thank you so much Solomon

Report message to a moderator

Previous Topic:	Listagg over Listagg
Next Topic:	pipeline function call in a stored proc and loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 07:29:21 CDT 2023