Group by using values in a range 1

[Olutee360]

Hi All,

I'm having a little challenge coming up with a query. Here's the problem

I have a table of REF_NUMs and Amount. I want to group by using certain amount range and have the count of REF_NUMs in the different ranges. Below is a sample of my table.

REF_NUM TRAN_AMT
1612 2500
1613 51800000
1614 2170000
1615 100
1616 2442876.5
1617 25000
1618 250
1619 7000
1610 51500
1621 15000
1622 20

I want to group the REF_NUM with amount in the range <5000, >5001 and <50000,>50001 and know their respective counts.
Output should look like

Range Count
<5000 4
>5001 and <50000 3
>50001 11


Please how do i go about this

 

[Andrzejek]

What about the values of =5000, =5001, =50000 and =50001 ?
How would you count these?

You may want to investigate Oracle CASE Expression

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Olutee360]

Hi Andrzejek.

Thanks for the quick feedback.

The ranges can be <=5000, between 5001 and 50000, >=500001

Any help appreciated. Thanks

 

[Andrzejek]

Did you read the link I gave you?
You can have another field to Group By, something like:

[pre]
Select
Case When TRAN_AMT <= 5000 Then 1
When TRAN_AMT > 5000 And TRAN_AMT <= 500000 Then 2
When TRAN_AMT > 50000 Then 3 End As GroupByThisField
From MyTable
[/pre]
(Code not tested)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Olutee360]

Thanks Andy. The link was super helpful.