CODEWITHSUNDEEP
sqloracle-databasejoinself-join
robin g
robin g

Reputation: 61

Can someone explain to me how self join works

im really puzzled how self join works and there are only a few examples of selfjoins online and mostly it just shows how to know the manager of an employee in a table. So i have this table

ItemCode     ItemNo    ItemTotal
---------------------------------
CT1       |    A     |    20
CT1       |    A     |    30
CT2       |    A     |    40
CT2       |    A     |    10

I would like to Sum() column ItemTotal per ItemCode and ItemNo and use the Sum() to divide it by the original Itemtotal per ItemNo and ItemCode. For example in ItemCode CT1 and ItemNo A the sum of it is 50 and would like to divide it by 20 and 30 which is the original itemTotal for ItemCode CT1 and ItemNo A. The same will happen to the next different ItemCode and ItemNo. Also if you can provide a explanation that would be helpful. Cheers!

Expected Result

ItemCode       ItemNo     ItemTotal
-------------------------------------
CT1       |      A     |    0.4   ----20/50
CT1       |      A     |    0.6   ----30/50 
CT2       |      A     |    0.8
CT2       |      A     |    0.2

Upvotes: 0

Views: 65

Answers (3)

jarlh
jarlh

Reputation: 44786

Oracle probably has some fancy functionality to do this. But since I don't know Oracle that well, I'll do a simple correlated sub-query answer:

select
    ItemCode,
    ItemNo,
    ItemTotal * 1.0 / (
        select SUM(ItemTotal)
        from tablename t2
        where t2.ItemNo = t1.ItemNo
    )
from tablename t1

Perhaps you want to do 

cast(ItemTotal * 1.0 / (
    select SUM(ItemTotal)
    from tablename t2
    where t2.ItemNo = t1.ItemNo
) as decimal(1,1))

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

Here's the Oracle fancy functionality way of doing this:

select ItemCode,
       ItemNo,
       ratio_to_report(ItemTotal) over ( partition by ItemCode,ItemNo) 
from   my_table;

Note that this is arguably more robust than:

ItemTotal / sum(ItemTotal) over ( partition by ItemCode,ItemNo)

... as it implicitly traps division by zero errors and returns null, which would otherwise require:

ItemTotal / NullIf(sum(ItemTotal) over ( partition by ItemCode,ItemNo) , 0 )

Upvotes: 1

Aleksej
Aleksej

Reputation: 22959

You can do it with a single scan of your table:

with test(ItemCode, ItemNo, ItemTotal) as
(
  select 'CT1', 'A', 20 from dual union all
  select 'CT1', 'A', 30 from dual union all
  select 'CT2', 'A', 40 from dual union all
  select 'CT2', 'A', 10 from dual 
)
select ItemCode, ItemNo,
       ItemTotal / sum(ItemTotal) over ( partition by ItemCode,ItemNo) 
from test

For completeness, if you want a solution with a join, you can use:

select ItemCode,ItemNo, t1.ItemTotal / sum(t2.ItemTotal)
from test t1
     inner join test t2 
       using(ItemCode,ItemNo)
group by ItemCode, ItemNo, t1.ItemTotal

However the two approaches have different performances; the plan for the join solution:

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     8 |   336 |     8  (25)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     8 |   336 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     8 |   336 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST |     4 |    84 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST |     4 |    84 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

and for the single scan solution:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    84 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |      |     4 |    84 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST |     4 |    84 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Upvotes: 3

Related Questions