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
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)
16 People are following this question.
