Calculations between two rows in one table

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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


This was first published in January 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top