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

The in operator may be used if you know the exact value you want to return for at least one of the columns.

0 Answers  

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

0 Answers  

Define select, insert, create, delete, update, drop keywords

0 Answers  

What are sql ddl commands?

0 Answers  

How to select random rows from a table?

0 Answers  

What is partition by in sql?

0 Answers  

describe transaction-safe table types in mysql : sql dba

0 Answers  

What is clause in sql?

0 Answers  

what is the difference between inner and outer join? Explain with example. : Sql dba

0 Answers  

Which join is default?

0 Answers  

what is a trigger in mysql? Define different types of trigger. : Sql dba

0 Answers  

how will u find statistics of a database objects?

2 Answers   iFlex,