question

Olutee360 avatar image
Olutee360 asked

Group by using values in a range

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, between 5001 and 50000 and >=500001

oracleoracle-sql-developersql2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

As rows or as columns?

declare @YourTable table (REF_NUM int, TRAN_AMT numeric(18,2))
insert into @YourTable select 1612, 2500
insert into @YourTable select 1613, 51800000
insert into @YourTable select 1614, 2170000
insert into @YourTable select 1615, 100
insert into @YourTable select 1616, 2442876.5
insert into @YourTable select 1617, 25000
insert into @YourTable select 1618, 250
insert into @YourTable select 1619, 7000
insert into @YourTable select 1610, 51500
insert into @YourTable select 1621, 15000
insert into @YourTable select 1622, 20

select groupings.RangeDesc, count(*)
from (
    select 
        REF_NUM, 
        TRAN_AMT,  
        case 
            when TRAN_AMT <=5000 then '<=5000'
            when TRAN_AMT between 5001 and 500000 then '5001-50000'
            when TRAN_AMT >=500001 then '>=500001'
        end as RangeDesc
    from @YourTable
    )groupings
group by groupings.RangeDesc

RangeDesc  
---------- -----------
<=5000     4
>=500001   3
5001-50000 4

(3 rows affected)


select 
    sum(case when TRAN_AMT <=5000 then 1 else 0 end) as '<=5000',
    sum(case when TRAN_AMT between 5001 and 500000 then 1 else 0 end) as '5001-50000',
    sum(case when TRAN_AMT >=500001 then 1 else 0 end) as '>=500001'
from @YourTable

<=5000      5001-50000  >=500001
----------- ----------- -----------
4           4           3

(1 row affected)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Many Thanks Kev. This was super helpful. The query returned the desired result

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.