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
8561 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

Which are the different character-manipulation functions in sql?

1039

What is a sql driver?

985

What are the benefits of pl sql?

1106

What is ttitle and btitle?

1048

what is the difference between primary key and unique key? : Sql dba

1015

Explain the components of sql?

1043

What is on delete set null?

1070

What is nosql example?

1014

Explain the purpose of %type and %rowtype data types with the example?

973

Can you do multiple joins in sql?

1021

What is union and union all keyword in sql?

1017

What is cursor in pl sql with examples?

1010

What are stored procedures in mysql?

1005

what is cross join? : Sql dba

980

Can dml statements be used in pl/sql?

1060