Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Calculations between two rows in one table

I am trying to generate a report that calculates the difference between the number of visits and page views when pull_id=1 from when pull_id=2. The problem I am having is that I am only using one table and not joining to another table. In other words, how do I distinguish the page views and visits to make the subtraction?

```CREATE TABLE INSIGHT_STATS(
INSIGHT_STATS_ID    NUMBER(28) NOT NULL,
NUM_OF_VISITS       NUMBER(6),
NUM_OF_PAGE_VIEWS   NUMBER(6),
LAST_SEEN_DATE      DATE,
FIRST_SEEN_DATE     DATE,
VISIT_START_DTIME   DATE,
OMEDA_USER_ID       VARCHAR2(28),
PULL_ID             NUMBER(3)
);```

Even though you are dealing with only one table, what you have to do in the query is imagine that there are two copies of it. This is called a self-join.

```select t1.INSIGHT_STATS
, t2.(NUM_OF_VISITS) -
t1.(NUM_OF_VISITS) as DiffVisits
, t2.(NUM_OF_PAGE_VIEWS) -
t1.(NUM_OF_PAGE_VIEWS) as DiffPageviews
from INSIGHT_STATS t1
inner
join INSIGHT_STATS t2
on t1.INSIGHT_STATS_ID
= t2.INSIGHT_STATS_ID
where t1.PULL_ID = 1
and t2.PULL_ID = 2```

Notice that there's a join condition which matches values of INSIGHT_STATS_ID, which I assume is part of the primary key of this table. In other words, a single INSIGHT_STATS_ID value is in the table multiple times, each with a different PULL_ID value. If that's not the case, you should be able to modify the query accordingly. The important point is the all rows within the same INSIGHT_STATS_ID value are combined into all combinations of PULL_ID values, but the WHERE clause selects only the specific match out of all combinations.

Also, notice that the differences are calculated by subtracting PULL_ID=1 values from PULL_ID=2, assuming that PULL_ID=2 is later.