SELECT DISTANCE,COUNT(*) NO_OFCITY from (SELECT (CASE WHEN TO

I Have A Table Like This.

Cityno Cityname Mails
1 Bangalore 8km
2 Hsr Layout 20km
3 Mejistic 30km
4 Jayadeva 55km
5 Itpl 80km
6 Hebbal 115km

I Have Data Like This

I Want O/p Like This
Distance No.ofcity
0-50km 3
51-100km 2
101-150km 4

And So On

Question Posted / kavitha nedigunta

6 Answers
13501 Views
DELL, HCL, Oracle, I also Faced
E-Mail Answers

Answer Posted / kavitha nedigunta

SELECT DISTANCE,COUNT(*) NO_OFCITY
from
(SELECT (CASE WHEN TO_NUMBER(RTRIM(MAILS,'KM')) BETWEEN 0
AND 50 THEN '0-50'
WHEN TO_NUMBER(RTRIM(MAILS,'KM')) BETWEEN 51 AND
100 THEN '51-100'
ELSE '101-150' END) DISTANCE
FROM TEST001) A
GROUP BY DISTANCE
order by to_number(replace(DISTANCE,'-',''))

Is This Answer Correct ?

0 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is Collation Sensitivity ? What are the various type ?

1001

How will you distinguish a global variable with a local variable in pl/sql?

1203

How to revise and re-run the last sql command?

1218

What are all the ddl commands?

1244

What is the maximum size of sqlite database?

1155

What are the different parts of a package?

1154

What is sqlite format?

1265

What is mdb stand for?

1063

How to rename a table?

1076

What is the purpose of my sql?

1013

What is a memo field?

1019

What is field delimiter?

1114

What is row_number () in sql?

1025

What is on delete set null?

1137

How many primary keys can a table have?

1059