SQL query problem- group by
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL query problem- group by

  1. #1
    Join Date
    Jan 2010
    Posts
    3

    SQL query problem- group by

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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>
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Thumbs down Homework

    Quote Originally Posted by PAVB View Post
    I have my doubts about how sound are those business specs but here you go...
    Looks like HOMEWORK to me.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by LKBrwn_DBA View Post
    Looks like HOMEWORK to me.
    yeah... you are correct. Didn't have any coffee in my system yet when I tackle it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2010
    Posts
    3
    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.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I see ... changing the question, huh? we all around here love it when it happens.

    Quote Originally Posted by prarthana_amen View Post
    ... 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?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Jan 2010
    Posts
    3
    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.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width