Help in this SQL

[OraMaster]

Hi All,

Below is the table columns and data in it.

select * from cumtemp;

ID AMT
----------------------
1 100
2 150
3 170
4 200
5 200
6 240
7 280
8 343
9 354
10 390

Now I need to display result like below

Amt Range Count
-----------------------------------
100-200 3
200-300 4
300-400 3

Please let me know the right SQL for this. Please note that I have given only sample of data here. Data could be in any range of amount.
Let me know if you need more information on this.

[debasisdas]

kindly post what have you tried so far .

[jsmithstl]

Assuming the AMOUNT is a number column.....

SQL> select * from cumtemp;

ID AMOUNT
---------- ----------
1 100
2 150
3 170
4 200
5 200
6 240
7 280
8 343
9 354
10 390
11 1070
13 1919
14 1982

13 rows selected.



col AMT_RANGE format a15
col COUNT format 99999

select distinct to_char(round(a.amount,-2)-round(mod(a.amount,100),-2)) ||'-'||
                to_char(round(a.amount,-2)-round(mod(a.amount,100),-2)+100) as AMT_RANGE
      ,(select count(*)
          from cumtemp b
         where round(b.amount,-2)-round(mod(b.amount,100),-2) = round(a.amount,-2)-round(mod(a.amount,100),-2)) as COUNT
  from cumtemp a
 order by length(AMT_RANGE),AMT_RANGE;

SQL> @ar

AMT_RANGE COUNT
--------------- ------
100-200 3
200-300 4
300-400 3
1000-1100 1
1900-2000 2



Good Luck,
Jim

[OraMaster]

Assuming the AMOUNT is a number column.....

SQL> select * from cumtemp;

ID AMOUNT
---------- ----------
1 100
2 150
3 170
4 200
5 200
6 240
7 280
8 343
9 354
10 390
11 1070
13 1919
14 1982

13 rows selected.



col AMT_RANGE format a15
col COUNT format 99999

select distinct to_char(round(a.amount,-2)-round(mod(a.amount,100),-2)) ||'-'||
                to_char(round(a.amount,-2)-round(mod(a.amount,100),-2)+100) as AMT_RANGE
      ,(select count(*)
          from cumtemp b
         where round(b.amount,-2)-round(mod(b.amount,100),-2) = round(a.amount,-2)-round(mod(a.amount,100),-2)) as COUNT
  from cumtemp a
 order by length(AMT_RANGE),AMT_RANGE;

SQL> @ar

AMT_RANGE COUNT
--------------- ------
100-200 3
200-300 4
300-400 3
1000-1100 1
1900-2000 2



Good Luck,
Jim

Thanks! for you help.

[amitpatel66]

Try this query:

[code=oracle]
SELECT (amt * 100)||'-'||amt||'99' "Amt-Range",COUNT(*) Cnt FROM
(SELECT id,TRUNC(amount/100) amt,amount FROM cumtemp)
GROUP BY amt
[/code]

[OraMaster]

Try this query:

[code=oracle]
SELECT (amt * 100)||'-'||amt||'99' "Amt-Range",COUNT(*) Cnt FROM
(SELECT id,TRUNC(amount/100) amt,amount FROM cumtemp)
GROUP BY amt
[/code]

Thanks for the reply!