Oracle SQL: Triangular Series (1 3 6 10 15 21)

Triangular Series

1
1+2=3
(1+2)+3=6
(1+2+3)+4=10
(1+2+3+4)+5=15

The general representation of a triangular number is dn= 1 + 2 + 3 + 4 +...+ (n-2) + (n-1) + n,


SQL for generating Triangular Series


select sum(b) from
(select level a from dual connect by level < 10) x,
(select level b from dual connect by level < 10) y
where b<=a
group by a
order by 1;

OUTPUT
---------------------------

1
3
6
10
15
21
28
36
45


Related Posts:
ORA-01436: CONNECT BY loop in user data
http://nimishgarg.blogspot.in/2011/02/ora-01436-connect-by-loop-in-user-data.html

Connect By Prior (Hierarchical Query)
http://nimishgarg.blogspot.in/2010/02/oracle-connect-by-prior-hierarchical.html

Get All Month or Week Days Names
http://nimishgarg.blogspot.in/2010/01/oracle-sql-get-all-month-names-jan-to.html

Alpha Numeric Counter Or Sequence
http://nimishgarg.blogspot.in/2011/04/alpha-numeric-counter-or-sequence.html

Oracle: Fibonacci Series by SQL
http://nimishgarg.blogspot.in/2010/08/oracle-fibonacci-series-by-sql.html

6 comments:

  1. The Same Can Be Achieved With

    SELECT
    ( N * ( N + 1 ) ) / 2
    FROM
    (
    select level N from dual connect by level < 10
    )

    ReplyDelete
    Replies
    1. Or even shorter ...

      select (level * (level + 1))/2 from dual connect by level < 10

      Delete
  2. This post gives single method for creating SQL triangular series. I got this problem in my exam but at that I wasn't aware of this. This time I used the query and output is visible. I think this is easiest possible way for triangular series. Thanks for this post.

    ReplyDelete
  3. Hey Please explain logic of connected by level
    select sum(b) from
    (select level a from dual connect by level < 10) x,
    (select level b from dual connect by level < 10) y
    where b<=a
    group by a
    order by 1;

    ReplyDelete
    Replies
    1. select level a from dual connect by level < 10
      the above query will generate 10 rows and value of a will be 1 to 10

      Delete