Find the mode of a set of values from a table?
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Working with the IF condition in nested cursor loops
The following demonstration, that I have modified only very slightly to make it fully generic, was provided to me by J.P. Vijaykumar [jp_vijaykumar @ yahoo.com] and I think is an excellent demonstration of the technique.

--TO FIND THE MODE OF A SET OF VALUES FROM A TABLE.
IF THE MODE IS "0" THEN TAKE THE NEXT HIGHER VALUE.
IF TWO VALUES EXIST EQUAL NUMBER OF TIMES, THEN
TAKE THE HIGHER NUMBER AS THE MODE.


If you use this in production my recommendation would be that you replace the nested cursor loops with nested BULK COLLECTs as demonstrated here [Click Here].

First let's build the demo tables and load the sample data.
CREATE TABLE t1 (
ename        VARCHAR2(20),
barcode      CHAR(5),
month_abbrev CHAR(3),
code         NUMBER);

CREATE TABLE t2 AS SELECT * FROM t1;

CREATE TABLE t3 AS SELECT * FROM t1;

CREATE TABLE t4 AS SELECT * FROM t1;

CREATE TABLE t5 AS SELECT * FROM t1;

INSERT INTO t1 VALUES ('JP', '06-17', 'JUN', 15);
INSERT INTO t1 VALUES ('LAKSHMI', '06-17', 'OCT', 5);
INSERT INTO t1 VALUES ('VINAY', '00-00', 'SEP', 14);
INSERT INTO t1 VALUES ('VENKATA', '09-22', 'AUG', 12);
INSERT INTO t1 VALUES ('VINEELA', '09-22', 'JAN', 4);
INSERT INTO t1 VALUES ('VEEKSHA', '00-00', 'SEP', 14);

INSERT INTO t2 VALUES ('SUBBARAO', NULL, NULL, 0);
INSERT INTO t2 VALUES ('PAPADEVI', NULL, NULL, 0);
INSERT INTO t2 VALUES ('BABU', '05-31', 'JUL', 7);
INSERT INTO t2 VALUES ('DURGA', '05-31', 'JUL', 7);
INSERT INTO t2 VALUES ('KIRAN', '00-00', 'SEP', 22);
INSERT INTO t2 VALUES ('ADITHYA', '00-00', 'SEP', 5);
INSERT INTO t2 VALUES ('NAGAMANI', '09-15', 'APR', 18);
INSERT INTO t2 VALUES ('SHAMBHU', '09-15', 'JAN', 20);
INSERT INTO t2 VALUES ('BHARADWAJ', '00-00', 'JAN', 27);
INSERT INTO t2 VALUES ('SUBHASH', '00-00', 'NOV', 3);
INSERT INTO t2 VALUES ('NAGESWARI', '02-24', 'SEP', 19);
INSERT INTO t2 VALUES ('SRINIVAS', '02-24', 'SEP', 19);
INSERT INTO t2 VALUES ('MAHESH', '00-00', 'JAN', 29);
INSERT INTO t2 VALUES ('MANASA', '00-00', 'APR', 12);

INSERT INTO t3 VALUES ('SUNDARAM', NULL, NULL, 0);
INSERT INTO t3 VALUES ('SAVITRI', NULL, NULL, 0);
INSERT INTO t3 VALUES ('VIJAYA', NULL, 'JAN', 1);
INSERT INTO t3 VALUES ('BALU', NULL, NULL, 0);

INSERT INTO t4 VALUES ('RAMAMOHAN', '05-11', 'MAY', 14);
INSERT INTO t4 VALUES ('MAHALAKSHMI', '05-11', 'APR', 26);
INSERT INTO t4 VALUES ('MALIKA', '08-28', 'DEC', 7);
INSERT INTO t4 VALUES ('KARTHIK', '08-28', 'DEC', 7);

INSERT INTO t5 VALUES ('SURYANARAYANA', NULL, 'JAN', 1);
INSERT INTO t5 VALUES ('SUBHADRA', NULL, NULL, 0);
INSERT INTO t5 VALUES ('RAMA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('SRINIVAS', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('KARTHIK', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('DIVYA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('NAGU', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('SIVA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('SHREYA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('MARUTHI', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('GAYATRY', NULL, 'SEP', 8);

COMMIT;
Now that the tables have been created and loaded take a look at the data and how it groups
SELECT * FROM t1;

SELECT * FROM t2;

SELECT * FROM t3;

SELECT * FROM t4;

SELECT * FROM t5;


SELECT code, COUNT(*)
FROM t1
GROUP BY code
ORDER BY 2 DESC, 1 DESC;

SELECT code, COUNT(*)
FROM t2
GROUP BY code
ORDER BY 2 DESC, 1 DESC;

SELECT code, COUNT(*)
FROM t3
GROUP BY code
ORDER BY 2 DESC, 1 DESC;

SELECT code, COUNT(*)
FROM t4
GROUP BY code
ORDER BY 2 DESC, 1 DESC;

SELECT code, COUNT(*)
FROM t5
GROUP BY code
ORDER BY 2 DESC, 1 DESC;
There is a smal view that is required by the code so build it next and examine its output.
CREATE OR REPLACE VIEW tview AS
SELECT 1 ename, 2 code
FROM dual;

SELECT * FROM tview;
And finally the anonymous block
set serverout on

DECLARE
 v_strg VARCHAR2(1024);
 v_mode NUMBER;
 v_rows NUMBER;
BEGIN
  FOR c1 IN (
    SELECT table_name FROM user_tables WHERE table_name LIKE 'T%' ORDER BY 1
            ) LOOP
    v_strg:='CREATE OR REPLACE VIEW tview AS SELECT ename, code FROM ' || c1.table_name;
    execute immediate v_strg;
    dbms_output.put_line(c1.table_name);
  v_mode:=0;
  v_rows:=0;

    FOR c2 IN (
      SELECT code, COUNT(*) num_rows FROM tview GROUP BY code ORDER BY 2 DESC, 1 DESC
              ) LOOP
      IF ((v_mode = 0) AND (v_rows = 0)) THEN
        v_mode := c2.code;
        v_rows := c2.num_rows;
      ELSIF ((v_mode = 0) AND (v_rows <> 0)) THEN
        v_mode :=c2.code;
        v_rows :=c2.num_rows;
      END IF;
    END LOOP;
    dbms_output.put_line(v_mode||' '||v_rows);
  END LOOP;
END;
/
J.P. also provided the following references that will likely be of great value if you wish to look deeper into the subject.
Reference:
http://www.purplemath.com/modules/meanmode.htm
http://www.mathgoodies.com/lessons/vol8/mode.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17329730362010
http://www.dbasupport.com/oracle/ora10g/multi_table_loop.shtml
J.P. Provided two alternative solutions here is the second example in which creates a view and makes use of ROWNUM to obtain the solution.
CREATE OR REPLACE VIEW tview AS
SELECT 1 ename, 2 code
FROM dual;

set serveroutput on

DECLARE
 v_strg VARCHAR2(1024);
 v_mode NUMBER;
 v_rows NUMBER;
BEGIN
  FOR c1 IN (
    SELECT table_name FROM user_tables WHERE table_name LIKE 'T%' ORDER BY 1
            ) LOOP
    v_strg:='CREATE OR REPLACE VIEW tview AS SELECT code, COUNT(*) num_rows ' ||
            'FROM ' || c1.table_name || ' GROUP BY code ORDER BY num_rows DESC, code DESC';
    execute immediate v_strg;
    dbms_output.put_line(c1.table_name);
    execute immediate 'SELECT code, num_rows FROM tview WHERE code <> 0 AND rownum =1'
    INTO v_mode, v_rows;
    dbms_output.put_line(v_mode||' '||v_rows);
  END LOOP;
END;
/
And here is the third making use of the CASE function
CREATE OR REPLACE VIEW tview AS
SELECT 1 ename, 2 code
FROM dual;

set serveroutput on

DECLARE
 v_strg VARCHAR2(1024);
 v_mode NUMBER;
 v_rows NUMBER;
BEGIN
  FOR c1 IN (
    SELECT table_name FROM user_tables WHERE table_name LIKE 'T%' ORDER BY 1
            ) LOOP
    v_strg:='CREATE OR REPLACE VIEW tview AS SELECT ename, code FROM ' || c1.table_name;
    execute immediate v_strg;
    dbms_output.put_line(c1.table_name);

    FOR c2 IN (
      SELECT CASE WHEN (
        SELECT code FROM (
          SELECT rownum row_num, t.* FROM (
            SELECT code, COUNT(*) num_rows FROM tview GROUP BY code ORDER BY num_rows DESC) t)
          WHERE row_num=1) = 0
          AND (
            SELECT COUNT(*) FROM (
              SELECT code, COUNT(*) num_rows
              FROM tview
              GROUP BY code
              ORDER BY num_rows DESC)) > 1 THEN
                     (
        SELECT code FROM (
          SELECT rownum row_num, t.* FROM (
            SELECT code, COUNT(*) num_rows
            FROM tview
            GROUP BY code
            ORDER BY num_rows desc) t)
        WHERE row_num=2)
      ELSE
                  (
        SELECT code FROM (
          SELECT rownum row_num, t.* FROM (
            SELECT code, COUNT(*) num_rows
            FROM tview
            GROUP BY code
            ORDER BY num_rows desc) t)
          WHERE row_num=1) END code FROM dual) LOOP
      SELECT num_rows
      INTO v_rows
      FROM(
        SELECT code, COUNT(*) num_rows
        FROM tview
        GROUP BY code
        ORDER BY num_rows DESC, code DESC)
      WHERE code = c2.code;
      dbms_output.put_line(c2.code || ' ' || v_rows);
    END LOOP;
  END LOOP;
END;
/
I really appreciate the donation of this code and hope everyone finds it of value to gain a deeper appreciation of the power and flexibility of PL/SQL.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved