Home » SQL & PL/SQL » SQL & PL/SQL » grouping logic (oracle 11g)
grouping logic [message #684218] Sat, 24 April 2021 12:39 Go to next message
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
Re: grouping logic [message #684219 is a reply to message #684218] Sat, 24 April 2021 13:16 Go to previous messageGo to next message
EdStevens
Messages: 1321
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.
Re: grouping logic [message #684221 is a reply to message #684219] Sat, 24 April 2021 16:34 Go to previous messageGo to next message
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


Re: grouping logic [message #684222 is a reply to message #684221] Sun, 25 April 2021 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3063
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.
Re: grouping logic [message #684242 is a reply to message #684224] Mon, 26 April 2021 20:01 Go to previous message
yashi7777
Messages: 42
Registered: March 2016
Member
Thank you so much Solomon
Previous Topic: Listagg over Listagg
Next Topic: pipeline function call in a stored proc and loop
Goto Forum:
  


Current Time: Tue Jun 22 00:19:09 CDT 2021