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,
  INSIGHT_COOKIE_ID   VARCHAR2(28),
  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.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.