Oracle SQL to roll up counts into ranges

Question

Possible Duplicate:
Oracle: how to “group by” over a range?

Let's say I have data that looks like this:

Item             Count
========         ========
1                123
2                1
3                47
4                117
5                18
6                466
7                202

I want to create a query that gives me this:

Count Start       Count End        Occurrences
===========       ===========      ===========
0                 100              3
101               200              2
201               300              1
301               400              0
401               500              1

Basically, I want to take a bunch of counts and group them into ranges for statistical rollups. I don't think I'm using the right keywords to find the answer to this. I am going against Oracle, though if there is an ANSI SQL answer I'd love to have it.

Voting to close my own question since I found the duplicate... — RationalGeek, Jun 18 '12 at 20:04
My understanding is that duplicates are preferable to deletes since people might end up using different keywords and stumble on the dupe. — RationalGeek, Jun 18 '12 at 22:02

score 1 · Accepted Answer · answered Jun 18 '12 at 23:43

select
    a.mini,
    a.maxi,
    count(a.item)
from
(
    select
        table.item,
        case (table.counter)
            when counter>=0 and counter<=100 then 0
            when counter>100 and counter<200 then 101
            when ....
        end as mini
        table.item,
        case (table.counter)
            when counter>=0 and counter<=100 then 100
            when counter>100 and counter<200 then 201
            when ....
        end as maxi
    from
        table
) a
group by
    a.mini,
    a.maxi

score 0 · Answer 2 · answered Jun 18 '12 at 20:11

0

One way is using CASE statements. If you want it to be scalable, try having the range in a separate table and use JOIN to count the occurence.

answered Jun 18 '12 at 20:11

user98534

195
3
4
9

Oracle SQL to roll up counts into ranges

2 Answers2