OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Aggregation

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

Aggregation [message #8080]

Fri, 25 July 2003 01:15

mangesh
Messages: 28
Registered: May 2002

Junior Member

Dear All,
I have following table
ro_id----------seq------------pro_id-----------value
-------------------------------------------------------
1--------------28--------------36---------------12
2--------------28--------------6----------------10
3--------------29------------- 17-------------- 30
4------------- 29------------- 6 -------------- 6
5------------- 29------------- 38-------------- 19
6------------- 29------------- 15-------------- 2394
7------------- 29------------- 17-------------- 228
8------------- 31------------- 44-------------- 33
I just want to write a query to sum the "value" where the value of the field "seq and pro_id" are same and all other rows should be display as it is
e.g in above table combination of " seq and pro_id" is same for ro_id=3 and ro_id=7 so it will create one row for this with sum of "value+ i.e. 30+228=258
Do i need to write a query or a cursor (pl/sql)
How it would be ?
TIA
Mangesh

Report message to a moderator

Re: Aggregation [message #8081 is a reply to message #8080]

Fri, 25 July 2003 02:14

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Here's one way to do it:

SQL> select a.ro_id
  2       , a.seq
  3       , a.pro_id
  4       , b.value
  5    from your_table a
  6       , ( select seq
  7                , pro_id
  8                , sum(value) value
  9             from your_table
 10            group by seq, pro_id
 11         ) b
 12   where a.pro_id = b.pro_id
 13     and a.seq    = b.seq;

     RO_ID        SEQ     PRO_ID      VALUE
---------- ---------- ---------- ----------
         2         28          6         10
         4         29          6          6
         6         29         15       2394
         3         29         17        <B>258</B>
         7         29         17        <B>258</B>
         1         28         36         12
         5         29         38         19
         8         31         44         33

8 rows selected.

HTH,
MHE

Report message to a moderator

Re: Aggregation [message #8084 is a reply to message #8081]

Fri, 25 July 2003 02:23

mangesh
Messages: 28
Registered: May 2002

Junior Member

Hi,
That is really helpful
But How could i avoid the duplication I need only one record ( want to merge both the records)i.e.either 3 or 7 i.e. only one 258
Thanks
Mangesh

Report message to a moderator

Re: Aggregation [message #8085 is a reply to message #8080]

Fri, 25 July 2003 02:32

mangesh
Messages: 28
Registered: May 2002

Junior Member

Hi Maaher,
That is really helpful
But How could i avoid the duplication I need only one record ( want to merge both the records)i.e.either 3 or 7 i.e. only one 258
Thanks
Mangesh

Report message to a moderator

Re: Aggregation [message #8086 is a reply to message #8084]

Fri, 25 July 2003 04:32

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Just take the inner select. I created the construction since I figured you wanted ro_id to be included in the select, because you stated:
(quote)
----------------------------------------------------------------------
and all other rows should be display as it is

----------------------------------------------------------------------
(end quote)
Now, without ro_id, it'll be sth like this:

SQL> select seq
  2       , pro_id
  3       , sum(value) value
  4    from test
  5   group by seq, pro_id;

       SEQ     PRO_ID      VALUE
---------- ---------- ----------
        28          6         10
        28         36         12
        29          6          6
        29         15       2394
        29         17        258
        29         38         19
        31         44         33

7 rows selected.

With the group by, ro_id cannot be included in the select because Oracle cannot identify which of the ro_id's it should display for each group, but you could use a group function for that too. Say, the largest of every group:

SQL> select max(ro_id) ro_id
  2       , seq
  3       , pro_id
  4       , sum(value) value
  5    from test
  6   group by seq, pro_id
  7  /

     RO_ID        SEQ     PRO_ID      VALUE
---------- ---------- ---------- ----------
         2         28          6         10
         1         28         36         12
         4         29          6          6
         6         29         15       2394
         7         29         17        258
         5         29         38         19
         8         31         44         33

7 rows selected.

SQL>

Report message to a moderator

Previous Topic:	Send mails using PL/SQL in oracle.
Next Topic:	What is Ref cursor ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:51:33 CDT 2024