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




Answers were Sorted based on User's Feedback



Answer / prativa mishra

select range distance,count(range) no_of_city
from
(SELECT cityname,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 CITY1)
group by range

Is This Answer Correct ?    20 Yes 2 No

Answer / janani

select '0-50' distance, count(*) No.ofcity from table
where mails between 0 and 50
union
select '51-100' distance, count(*) No.ofcity from table
where mails between 51 and 100
union
select '101-150' distance, count(*) No.ofcity from table
where mails between 101 and 150

Is This Answer Correct ?    5 Yes 1 No

Answer / anil

data abc;
set xyz;
count=1;
length Distance$ 10;
if mails<=50 then Distance='0-50';
else if 50 < mails <= 100 then Distance='51-100';
else if 100<mails <= 150 then Distance='101-150';
run;
proc print;
run;

proc sql;
select Distance,sum(count)as NO_OFCITY
from abc
group by Distance;
quit;

Is This Answer Correct ?    1 Yes 0 No




Answer / js

SELECT R,COUNT(R) FROM
(
SELECT cityname,mail,
CASE WHEN SUBSTR(MAIL,1,INSTR(MAIL,'KM' )-1) BETWEEN 0 AND 50 THEN '0-50'
WHEN SUBSTR(MAIL,1,INSTR(MAIL,'KM' )-1) BETWEEN 51 AND 100 THEN '51-100'
ELSE '101-150' END R FROM CITY) GROUP BY R;

Is This Answer Correct ?    1 Yes 0 No

Answer / kavitha nedigunta

SELECT DISTANCE,COUNT(*) NO_OFCITY
from
(SELECT (CASE WHEN TO_NUMBER(RTRIM(MAILS,'KM')) BETWEEN 0
AND 50 THEN '0-50'
WHEN TO_NUMBER(RTRIM(MAILS,'KM')) BETWEEN 51 AND
100 THEN '51-100'
ELSE '101-150' END) DISTANCE
FROM TEST001) A
GROUP BY DISTANCE
order by to_number(replace(DISTANCE,'-',''))

Is This Answer Correct ?    0 Yes 0 No

Post New Answer



More SQL PLSQL Interview Questions

what is the difference between the query and corelated query

8 Answers   TCS, HSBC, IBM, Xenosoft,


What is oracle ? why we should go for oracle database instead of diffrent databases available in the industry.

5 Answers   Polaris,


What is the use of cursor ? how cursor allocate context area for executing the sql statement?

4 Answers   HCL,


Whis is not false in primary key?

0 Answers   Fintellix,


IF EMP HAS 2 ROWS,DEPT HAS 4 ROWS.WHATS THE RESULT OF SELECT * FROM EMP,DEPT;

1 Answers  


how to remove records from table? no name 1 a 2 b 1 a 2 b 3 c

8 Answers   Oracle,


why use cursors?

5 Answers   Oracle,


Can we use more than one null value for unique key?

31 Answers   Wipro, A1 Technology,


What is the Difference between Procedure and Function.Can we call a Function in a DML?

2 Answers   TCS,


what is ref cursor in pl/sql?

3 Answers  


how to delete duplicate rows from a specified table(only single table) how do you know which join is need to be used

11 Answers   UST,


What are the two parts of a procedure ?

6 Answers   Hi Caliber IT,




Categories