increment plsql

[747576]

Hi,

I have 2 tables one called NEWTABLE and another called FREQUENCY. The table NEWTABLE contains an id field and a value field, I want to automatically (dynamically) create the records in the FREQUENCY table using the NEWTABLE.

I want to count how many values fall between 96-97, 98-99 and so on, please see the table in the script below. The NEWTABLE would have varying values, so I would need to first find the max and min value and then increment the FREQUENCY field by 2 and then calculate the COUNT.

I tried this with the sequence function but couldn't use a select script to define the min and max value.

Thank you.

DROP TABLE NEWTABLE;
DROP TABLE FREQUENCY;
CREATE TABLE NEWTABLE(ID NUMERIC(10), OVALUE NUMERIC(10));
CREATE TABLE FREQUENCY(FREQUENCY NUMERIC(10), OCOUNT NUMERIC(10));

INSERT INTO NEWTABLE(ID,OVALUE) VALUES('1','96');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('2','97.5');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('3','97');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('4','97');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('5','98');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('6','98');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('7','98');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('8','99');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('9','100');
INSERT INTO NEWTABLE(ID,OVALUE) VALUES('10','97');

INSERT INTO FREQUENCY(FREQUENCY,OCOUNT) VALUES('96','5');
INSERT INTO FREQUENCY(FREQUENCY,OCOUNT) VALUES('98','4');
INSERT INTO FREQUENCY(FREQUENCY,OCOUNT) VALUES('100','1');

SELECT * FROM NEWTABLE;
SELECT * FROM FREQUENCY;

 

[taupirho]

Try this

select
floor(ovalue/2)*2, count(*)
from newtable
group by floor(ovalue/2)*2



In order to understand recursion, you must first understand recursion.