Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2013
    Posts
    3

    Unanswered: Giant Case Statement

    I have a table in Oracle DB withe order count and unique IDs as columns:

    Order Count ID
    2 1
    3 2
    4 4
    4 5
    11 6
    20 8
    35 9
    19 12
    Now I want to get a count of IDs grouped by order and separated into different buckets. For e.g. all the orders that have value <= 10 ,I need to separate the order bucket by "1" and all the orders that have value >10 and <100 I need to separate the order bucket by 10.

    The output for above table should be:
    Count(ID) Order
    1 2
    1 3
    2 4
    3 11-20
    1 31-40

    I don't want to use case statement since I have a lot of such bucket based on which I need to group the data.

    Please suggest how can I write the SQL query.
    Last edited by Nalin21; 11-17-13 at 00:57.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Create a table with your bucket information, specifically the lower and upper bound for each bucket. Use a LEFT JOIN from the bucket table to your data table, and group by the values in the bucket table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You wrote
    ...,I need to separate the order bucket by "1" and all the orders that have value >10 and <100 I need to separate the order bucket by 10.
    But,
    The output for above table should be:
    Count(ID) Order
    1 2
    1 3
    2 4
    3 11-20
    0 21-30
    1 31-40
    Aren't other order bucket(e.g. 1, 5, ... 9, 10, 41-50, 51-60, so on...) neccesary?

    And, if they were not neccesary,
    why order bucket 21-30 was included in the output?
    Last edited by tonkuma; 11-17-13 at 00:40.

  4. #4
    Join Date
    Nov 2013
    Posts
    3
    Yes, all the buckets are necessary. I was just trying to give the output based on the input. My bad ! 21-30 should not have been included in the output. (I have edited the output)

    Thanks.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Not tested.
    Code:
    SELECT COUNT(ID)
         , order_bucket
     FROM  (SELECT t.*
                 , CASE
                   WHEN order <= 10 THEN
                        TO_CHAR(order)
                   WHEN order BETWEEN  11
                                  AND 100 THEN
                        TO_CHAR( TRUNC(order - 1 , -1) + 1)
                        || '-' ||
                        TO_CHAR( TRUNC(order + 9 , -1) )
                   END  AS order_bucket
             FROM  <<your-table>> AS t
           )
     GROUP BY
           order_bucket
     ORDER BY
           LENGTH(order_bucket)
         , order_bucket
    ;
    Last edited by tonkuma; 11-17-13 at 02:04.

Tags for this Thread

Posting Permissions

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