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 |
SQL> select * from quest;
Cityno cityname mails
---------- -------------------- ----------
1 bangalore 8km
2 hsr layout 20km
3 mejistic 30km
4 jayadeva 55km
5 itpl 80km
6 hebbal 115km
6 rows selected.
SQL> with data as (
2 select level as lvl,lag(level,1,0) over(order by level) as pre_lvl
3 from dual
4 where mod(level,50) = 0
5 connect by level <=150)
6 select count(cityno),pre_lvl||'-'||lvl
7 from quest
8 ,data
9 where to_number(replace(mails,'KM',0))/10 between pre_lvl and lvl
10 group by lvl,pre_lvl;
Is This Answer Correct ? | 0 Yes | 0 No |
How many rows will return from dual table?
What are the differences between in and exists clause?
How do I partition a table in sql?
How do I edit a stored procedure?
wa procedure to return the month and the no'f developers joined in each month.
There is a sequence with min value 100. I want to alter this sequence to min value as 101. If the table has already data in the sequence column as 100,101,102... Is it possible to do so ?
What found sql?
How do you optimize a query?
Is it possible to include an insert statement on the same table to which the trigger is assigned?
i have a table emp and columns ename,empno,mgr_id,i need ename,manager name as result i.e employee respective manager.. example empno ename mgr_id 1 john 3 2 paul 3 3 smith 1 4 kevin 1 5 stewart 2 result has to look like this ename manager john smith paul smith smith john kevin john stewart paul can u plz help me out in this.....
How do you select unique values in sql?
What are the differences between Database Trigger and Integrity constraints ?