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.