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 sqlerrm?

0 Answers  

Which data dictionary views have the information on the triggers that are available in the database?

0 Answers  

What is a sql*loader control file?

0 Answers  

How do you change a value in sql?

0 Answers  

What is the difference between instead of trigger and after trigger?

0 Answers  

What are functions in sql?

0 Answers  

How to check if a column is nullable before setting to nullable?

0 Answers  

Why do we use joins in sql?

0 Answers  

Why is sharding used?

0 Answers  

what is query cache in mysql? : Sql dba

0 Answers  

Is record in pl sql?

0 Answers  

what is 'mysqlimport'? : Sql dba

0 Answers