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 are enums used for in mysql? : Sql dba

0 Answers  

What does select count (*) mean in sql?

0 Answers  

What will be the output for the below Query Select 'High' from dual where null = null;

12 Answers   Infosys, Satyam,

Why does sql need a server?

0 Answers  

How do I run a pl sql program?

0 Answers  

Regarding joins what are the differences you observed in oracle 9i and sql server?

3 Answers   Choice Solutions,

How is a PL/SQL code compiled?

0 Answers  

Explain mutating table error.

0 Answers  

What are sql constraints?

0 Answers  

Mention what plvcmt and plvrb does in pl/sql?

0 Answers  

What is the difference between inner join and outer join?

0 Answers  

How to read xml file in oracle pl sql?

0 Answers