robin g robin g - 2 months ago 10
SQL Question

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

Answer

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 |
---------------------------------------------------------------------------