Help in this SQL  | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| |
Hi All,
Below is the table columns and data in it.
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.
|  | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,511
| | | re: Help in this SQL
kindly post what have you tried so far .
| | Newbie | | Join Date: Jun 2007
Posts: 12
| | | re: Help in this SQL
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
| | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: Help in this SQL
Quote:
Originally Posted by 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 Thanks! for you help.
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Help in this SQL
Try this query: -
SELECT (amt * 100)||'-'||amt||'99' "Amt-Range",COUNT(*) Cnt FROM
-
(SELECT id,TRUNC(amount/100) amt,amount FROM cumtemp)
-
GROUP BY amt
-
| | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: Help in this SQL
Quote:
Originally Posted by amitpatel66 Try this query:
-
SELECT (amt * 100)||'-'||amt||'99' "Amt-Range",COUNT(*) Cnt FROM
-
(SELECT id,TRUNC(amount/100) amt,amount FROM cumtemp)
-
GROUP BY amt
-
Thanks for the reply!
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
