Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 12, 2016 - 10:04 am UTC

Last updated: September 12, 2016 - 4:32 pm UTC

Version: None

Viewed 1000+ times

You Asked

Hi,
I got this question in one interview using Self Join


Table

Employee Salary
1 1K
2 2K
3 3K
4 4K
5 5K


Output is
1 1K
2 3K
3 6K
4 10K
5 15K


What is the query using self join for the above output.

If employee is 1 print 1k,if it is 2 print sum(employee1 and 2) like this till 5th employee

and Chris said...

There's no need for a self join! It's just a matter of using analytics:

with emps as (
  select 1 id, 1000 sal from dual union all
  select 2 id, 2000 sal from dual union all
  select 3 id, 3000 sal from dual union all
  select 4 id, 4000 sal from dual union all
  select 5 id, 5000 sal from dual 
)
  select id, sum(sal) over (order by id) from emps;

ID  SUM(SAL)OVER(ORDERBYID)  
1   1,000                    
2   3,000                    
3   6,000                    
4   10,000                   
5   15,000


In the over clause, pass the columns that determine which order to do the running total. For example, you could do it from highest to lowest like so:

with emps as (
  select 1 id, 1000 sal from dual union all
  select 2 id, 2000 sal from dual union all
  select 3 id, 3000 sal from dual union all
  select 4 id, 4000 sal from dual union all
  select 5 id, 5000 sal from dual 
)
  select id, sum(sal) over (order by sal desc) from emps
  order  by id;

ID  SUM(SAL)OVER(ORDERBYSALDESC)  
1   15,000                        
2   14,000                        
3   12,000                        
4   9,000                         
5   5,000 


For more details on how to do this, see Connor's video:

https://www.youtube.com/watch?v=1k-D3NPmlvg

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Non analytics

Rajeshwaran Jeyabal, September 12, 2016 - 1:04 pm UTC

Incase if you don't like analytic (for what ever reason), and stick with self-join and aggregates,then....

drop table t purge;
create table t(emp_id int,sal int);
insert into t values(1,1000);
insert into t values(2,2000);
insert into t values(3,3000);
insert into t values(4,4000);
insert into t values(5,5000);
commit;

demo@ORA11G> select t1.emp_id,
  2     (select sum(t2.sal)
  3      from t t2
  4      where t2.emp_id <= t1.emp_id ) tot_sal
  5  from t t1
  6  /

    EMP_ID    TOT_SAL
---------- ----------
         1       1000
         2       3000
         3       6000
         4      10000
         5      15000

5 rows selected.

Chris Saxon
September 12, 2016 - 4:32 pm UTC

I can't think of a good reason not to use analytics. Thanks for the solution anyway.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.