SQL query problem- group by

[prarthana_amen]

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

[PAVB]

I have my doubts about how sound are those business specs but here you go...

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>

[LKBrwn_DBA]

I have my doubts about how sound are those business specs but here you go...

Looks like HOMEWORK to me.

[PAVB]

Looks like HOMEWORK to me.

yeah... you are correct. Didn't have any coffee in my system yet when I tackle it.

[prarthana_amen]

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.

[PAVB]

I see ... changing the question, huh? we all around here love it when it happens.

... in real world I would not know the value of pur_by ...

How could you know the PUR_BY ranges/groupings and at the same time not know the PUR_BY values?

Do you realize that quoted statement doesn't make any sense?

[prarthana_amen]

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.

[PAVB]

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.