question

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

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

·

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

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 ·

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.