I am trying to generate a report that calculates the difference between the number of visits and page views when...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.