hi, if mails field is numeric above query is correct but mails filed is

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

pls give me answer. i want urgent

Question Posted / senthil

6 Answers
8644 Views
I also Faced
E-Mail Answers

Answer Posted / senthil

hi,
if mails field is numeric above query is correct but mails filed is varchar that is contain 'km' so test my query....
Ex:
select RANGE distance,count(range) no_of_city
from
(SELECT citiname,mails,
CASE WHEN to_number(substr(mails,0,(instr(mails,'k')-1))) BETWEEN 0 AND 50 THEN '0-50'
WHEN to_number(substr(mails,0,(instr(mails,'k')-1))) BETWEEN 51 AND 100 THEN '51-100'
ELSE '101-150'
END RANGE
FROM test1) group by range

Is This Answer Correct ?

0 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Is sql microsoft?

1060

What is sql and also describe types of sql statements?

1069

What is sqlite format?

1204

what are ddl statements in mysql? : Sql dba

1208

What does trigger mean in psychology?

967

Which are sql * plus commands?

1132

How do I upgrade sql?

1005

What is bulk collect in pl sql?

1096

What do you mean by table in sql?

997

what is index? : Sql dba

1010

What is inner join in sql?

1086

Why do we need cursor in pl sql?

1139

Show the two pl/sql cursor exceptions.

1201

What are different types of keys?

1109

How to run sql commands in sql*plus?

1189