Results 1 to 5 of 5

Thread: categorise data

  1. #1
    Join Date
    Jan 2009
    Posts
    11

    Unanswered: categorise data

    I have the following data to display as a table.
    The table should display:
    location, total no of targets, number of targets in each cost range.
    The cost ranges are 3-4.99 and 5-6.99

    loc_id target_id cost range
    -- ---------- ----------
    1 291 5 6.99
    2 212 5 6.99
    2 231 5 6.99
    8 6 5 6.99
    8 12 3 4.99
    8 15 5 6.99
    8 198 5 6.99
    9 51 5 6.99
    9 239 5 6.99

    for example, location 8 has 3 targets in 5-6.99 cost range and 1 target in 3-4.99 cost range.
    This should display as follows.

    location | total no. of targets | no. of targets in cost range 3-4.99 | no. of targets in cost range 5-6.99
    -------- ------------------- ----------------------------------- ----------------------------------
    8 4 1 2

    how can i do this?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I sure hope the "range" column is just for display and you are not doing any "maths" with that. The range should go into a separate table with a start and end value defining the range and the main table should only reference the entries from the range table.

    Now for your problem. Something like the following should do what you want:

    Code:
    select distinct loc_id, 
           count(*) over (partition by loc_id) as "total no. of targets",
           case 
              when cost_range = '3-4.99' then count(*) over (partition by loc_id, cost_range)
              else null
           end as "no. of targets in cost range 3-4.99",
           case 
              when cost_range = '5-6.99' then count(*) over (partition by loc_id, cost_range)
              else null
           end as "no. of targets in cost range 5-6.99"
    from range 
    order by loc_id
    ;

  3. #3
    Join Date
    Jan 2009
    Posts
    11
    thx for ur help. i found the following and post for others who has a similar problem.

    sum (case when floor(cost) <1 then 1 else 0 end ) as "<1",
    sum (case when floor(cost) >=1 and floor(cost) <= 2 then 1 else 0 end ) as "1 – 2.99",
    sum (case when floor(cost) >=3 and floor(cost) <= 4 then 1 else 0 end ) as "3 – 4.99",
    sum (case when floor(cost) >=5 and floor(cost) <= 6 then 1 else 0 end) as "5 – 6.99",
    sum (case when floor(cost) >=7 then 1 else 0 end ) as ">=7"

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by madara79
    thx for ur help. i found the following and post for others who has a similar problem.

    sum (case when floor(cost) <1 then 1 else 0 end ) as "<1",
    sum (case when floor(cost) >=1 and floor(cost) <= 2 then 1 else 0 end ) as "1 2.99",
    sum (case when floor(cost) >=3 and floor(cost) <= 4 then 1 else 0 end ) as "3 4.99",
    sum (case when floor(cost) >=5 and floor(cost) <= 6 then 1 else 0 end) as "5 6.99",
    sum (case when floor(cost) >=7 then 1 else 0 end ) as ">=7"
    Which means that the data in your table is stored in a completely different way compared to the sample data of your initial post.
    Please remember to show the "real" sample data in the future!

  5. #5
    Join Date
    Jan 2009
    Posts
    11
    the requirement was quite complicated. so i thought of putting down an intermediate output and sought help to get the final output. sorry if i have confused you.

Posting Permissions

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