Answer / prasant kumar sahoo

Answer in Horizontal out put
select RANGE distance,count(range) no_of_city
from
(SELECT citiname,mails,
CASE WHEN MAILS BETWEEN 0 AND 50 THEN '0-50'
WHEN MAILS BETWEEN 51 AND 100 THEN '51-100'
ELSE '101-150'
END RANGE
FROM test1)
group by range;

Answer / ram

pls send me pl/sql script also

Answer / 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

Answer / anjan ghosh

create table xyz ( x varchar2(30),y varchar2(30),z number);

insert into xyz values (6 , 'HEBBAL' ,115);

select count(*) as No_of_city , '0-50' as Distance from xyz
where z between 0 and 50
union all
select count(*)as No_of_city ,'51-100' as Distance from
xyz where z between 51 and 100
union all
select count(*)as No_of_city ,'101-150' as Distance from
xyz where z between 51 and 100

Answer / apoorva garg

cityno cityname mails
1 BANGALORE 8KM
2 HSR LAYOUT 20KM
3 MEJISTIC 30KM
4 JAYADEVA 55KM
5 ITPL 80KM
6 HEBBAL 115KM

create table city(name varchar2(10), miles number);
insert into city values('bangalore',8)
insert into city values('hsr',20)
insert into city values('majestic',30)
insert into city values('jayadeva',55)
insert into city values('itpl',80)
insert into city values('hebbal',115)

DISTANCE NO.OFCITY
0-50KM 3
51-100KM 2
101-150KM 4


select * from city


select count(miles),miles
from
(select name,
CASE
WHEN (miles>=0 and miles <=50) THEN '0-50KM '
WHEN (miles>=51 and miles <=100) THEN '51-100KM '
WHEN (miles>=101 and miles <=150) THEN '101-151KM '
END miles
from city)

group by miles

Answer / prasant kumar sahoo

Here I am giving the query in SQL in Vertical Out put
----------------------------------------------
select * from (select (select count(*) from (select
count(citino) from test1 where mails between 0 and 50 group
by citino) t1) as "0-50",
(select count(*) from (select count(citino) from test1 where
mails between 51 and 100 group by citino) t2) as "51-100",
(select count(*) from (select count(citino) from test1 where
mails between 101 and 150 group by citino) t3) as "101-150"
from test1) "Result" where rownum=1;
---------------------------------------------------
if u want to Horizontal out put it need an another table
that store the range like 0-50,51-100 like. And then U just
Join this it so easy. If you need then, mail me
---------------------------------------------
if you need in Plsql also mail me I will send you

What is difference between cursor and trigger?

0 Answers  

How does cross join work in sql?

0 Answers  

Name three sql operations that perform a sort.

0 Answers  

overloading of stored procedure is possible in oracle?

3 Answers   Nelco,

How do I run a sql trace?

0 Answers  

define a variable representing the expression used to calculate on emps total annual remuneration.use the variable in a statement which finds all emps who can earn 30000 a year or more.

5 Answers  

what is the difference difference between procedure and packages

2 Answers   3i Infotech, Oracle,

can we call a procedure from a function?

9 Answers   Mind Tree,

What is application trigger?

0 Answers  

What is rtm stands for?

0 Answers  

How delete all data from table in sql?

0 Answers  

What does data normalization mean?

0 Answers