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
Answers were Sorted based on User's Feedback
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;
Is This Answer Correct ? | 6 Yes | 1 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
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
Is This Answer Correct ? | 0 Yes | 1 No |
How delete all data from table in sql?
What does count (*) mean in sql?
How many types of sql are there?
What is the difference between delete, truncate and drop command?
How to avoid using cursors? What to use instead of cursor and in what cases to do so?
What are different types of keys?
What is a loop in sql?
What is self-join and what is the requirement of self-join?
What is sql catalog?
How write primary and foreign key relationship between two tables without using constraints? u can use any of procedure/function/trigger and any sql?
sql query to get zero records from a table having n no of records
Can we use having without group by in sql?