Printable View
Hi,
I am facing an issue with data manipulation with sql query. I am having data as below and I need the output as shown below output.Any help would be of great helkp to me.
ITEM_ID PUR_BY AMOUNT CUST_ID DISCOUNT
1 AB 100 121 5
2 CD 200 121 4
3 EF 120 122 4
4 GH 130 122 3
5 IJ 140 123 4
Output
PUR_BY AMOUNT CUST_ID DISCOUNT
AB-CD 300 121 9
EF-GH 250 122 7
IJ 140 123 4
I have my doubts about how sound are those business specs but here you go...
Code:SQL>
SQL> create table mytable (
2 item_id number,
3 pur_by varchar2(2),
4 amount number,
5 cust_id number,
6 discount number)
7 ;
Table created.
SQL> insert into mytable values(1, 'AB', 100, 121, 5);
1 row created.
SQL> insert into mytable values(2, 'CD', 200, 121, 4);
1 row created.
SQL> insert into mytable values(3, 'EF', 120, 122, 4);
1 row created.
SQL> insert into mytable values(4, 'GH', 130, 122, 3);
1 row created.
SQL> insert into mytable values(5, 'IJ', 140, 123, 4);
1 row created.
SQL> commit;
Commit complete.
SQL> select decode(pur_by,'AB','AB-CD','CD','AB-CD','EF','EF-GH','GH','EF-GH',pur_by) as "PUR-BY",
2 cust_id,
3 sum(amount),
4 sum(discount)
5 from mytable
6 group by decode(pur_by,'AB','AB-CD','CD','AB-CD','EF','EF-GH','GH','EF-GH',pur_by), CUST_ID
7 order by decode(pur_by,'AB','AB-CD','CD','AB-CD','EF','EF-GH','GH','EF-GH',pur_by), CUST_ID;
PUR-B CUST_ID SUM(AMOUNT) SUM(DISCOUNT)
----- ---------- ----------- -------------
AB-CD 121 300 9
EF-GH 122 250 7
IJ 123 140 4
SQL>
Looks like HOMEWORK to me.Quote:
Originally Posted by PAVB
:rolleyes:
yeah... you are correct. Didn't have any coffee in my system yet when I tackle it.Quote:
Originally Posted by LKBrwn_DBA
Thanks for the quick reply, but my problem is that in real world I would not know the value of pur_by and based on cust_id I have to combine pur_by column.In the example above, I may have 3..n pur_by value for the same cust_id and I need to calculate their sum based on this. Request for your help again.
I see ... changing the question, huh? we all around here love it when it happens.
How could you know the PUR_BY ranges/groupings and at the same time not know the PUR_BY values?Quote:
Originally Posted by prarthana_amen
Do you realize that quoted statement doesn't make any sense?
Yes, you are right.. may be we can have another table inbetween with the mapping.In my case I am only concerned with cust_id, cant we group all the PUR_BY having the same cust_id. Otherwise I may have to introduced another mapping table.
If what you want is to group by CUST_ID you should be able to alter the query I provided and get it done in about 30 seconds.
Look at the query, understand what it does and customize it to your needs.