robin g - 1 year ago 57
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
``````

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 |
---------------------------------------------------------------------------
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download