Code: |
col1 ---- 10 20 30 40 50 60 70 80 90 |
Code: |
CREATE TABLE T1 (COL1 INTEGER); INSERT INTO T1 VALUES(10); INSERT INTO T1 VALUES(20); INSERT INTO T1 VALUES(30); INSERT INTO T1 VALUES(40); INSERT INTO T1 VALUES(50); INSERT INTO T1 VALUES(60); INSERT INTO T1 VALUES(70); INSERT INTO T1 VALUES(80); INSERT INTO T1 VALUES(90); |
Code: |
SELECT A.COL1 AS VAL1 ,COUNT(*) AS CNT FROM T1 A ,T1 B WHERE A.COL1 >= B.COL1 GROUP BY A.COL1 HAVING VALUE ( DECIMAL (COUNT(*) ,7, 2 ) / 1 , 0 ) = INTEGER ( COUNT(*) / 1 ); |
Code: |
---+---------+---------+-- VAL1 CNT ---+---------+---------+-- 10 1 20 2 30 3 40 4 50 5 60 6 70 7 80 8 90 9 |
Code: |
SELECT AVG(B.VAL1) FROM (SELECT DEC(A.COL1) AS VAL1 ,COUNT(*) AS CNT FROM T1 A ,T1 B WHERE A.COL1 >= B.COL1 GROUP BY A.COL1 HAVING VALUE ( DECIMAL (COUNT(*) ,7, 2 ) / 1 , 0 ) = INTEGER ( COUNT(*) / 1 )) B WHERE B.CNT = (SELECT ROUND(DEC(COUNT(*)+0)/2,0) FROM T1) OR B.CNT = (SELECT ROUND(DEC(COUNT(*)+1)/2,0) FROM T1) ; |
Code: |
50.0000 |
Code: |
INSERT INTO T1 VALUES(100); |
Code: |
SELECT AVG(B.VAL1) FROM (SELECT DEC(A.COL1) AS VAL1 ,COUNT(*) AS CNT FROM T1 A ,T1 B WHERE A.COL1 >= B.COL1 GROUP BY A.COL1 HAVING VALUE ( DECIMAL (COUNT(*) ,7, 2 ) / 1 , 0 ) = INTEGER ( COUNT(*) / 1 )) B WHERE B.CNT = (SELECT ROUND(DEC(COUNT(*)+0)/2,0) FROM T1) OR B.CNT = (SELECT ROUND(DEC(COUNT(*)+1)/2,0) FROM T1) ; |
Code: |
55.0000 |
output generated using printer-friendly topic mod. All times are GMT - 5 Hours