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:

Anuj SharmaSeptember 3, 2010 at 6:15 PM
The Same Can Be Achieved With

SELECT
( N * ( N + 1 ) ) / 2
FROM
(
select level N from dual connect by level < 10
)
Nimish GargNovember 24, 2010 at 4:40 PM
thats good tooo :)
what is sap ecc 6.0December 12, 2011 at 10:48 PM
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.
AnonymousMarch 11, 2015 at 8:24 PM
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;