How to compute numbers that the sum of all its digits are divisible by three in a SQL statement
February 8th, 2007 By Frank Zhou
The following SQL pattern can be used to compute the numbers that are divisible by three, and the sum of all its digits are also divisible by three.
SQL> variable input number
SQL> exec :input := 65
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> SELECT TO_NUMBER(IN_STR) AS NUM,
2 MIN(IN_STR) OVER ( ) AS MIN,
3 MAX(IN_STR) OVER ( ) AS MAX,
4 MEDIAN(IN_STR) OVER ( ) AS MEDIAN
5 FROM ( SELECT LEVEL * 3 IN_STR FROM dual CONNECT BY LEVEL * 3 <= :input)
6 CONNECT BY PRIOR IN_STR = IN_STR
7 AND LEVEL <= LENGTH(IN_STR)
8 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
9 GROUP BY IN_STR
10 HAVING MOD(SUM(TO_NUMBER( SUBSTR(IN_STR, LEVEL, 1))),3) = 0;
NUM MIN MAX MEDIAN
---------- ---------- ---------- ----------
3 3 63 33
6 3 63 33
9 3 63 33
12 3 63 33
15 3 63 33
18 3 63 33
21 3 63 33
24 3 63 33
27 3 63 33
30 3 63 33
33 3 63 33
36 3 63 33
39 3 63 33
42 3 63 33
45 3 63 33
48 3 63 33
51 3 63 33
54 3 63 33
57 3 63 33
60 3 63 33
63 3 63 33
21 rows selected.
SQL> spool off;