#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Location
    In Transit HQ
    Posts
    2
    Rep Power
    0

    Count of users who does multiple activities


    I have a table with the following:

    Each student has a unique ID (studentID).
    Some students buy from the bookstore (websiteA).
    Some students use the bank online (websiteB).
    I would like to get a count of students (count of distinct studentID) who visit both websiteA and websiteB.

    How do I query this?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Please show us your table structure(s). Ideally as a CREATE TABLE statement.

    If you want to make helping even easier, provide some sample data as INSERT INTO statement (or put everything into http://sqlfiddle.com).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Location
    In Transit HQ
    Posts
    2
    Rep Power
    0
    Here are the pertinent parts:

    CREATE TABLE jnam.fcreg
    (
    id bigint,
    date_key integer,
    user_key integer,
    website_name character varying (255),
    create_time timestamp with time zone,
    address inet
    )

    Each time a user visits either websiteA or websiteB, a record is created. So if a user visits websiteA 10 times in a month, then I will get 10 records for this user, each with websiteA as the value for website_name. For each user_key, the website_name value can be websiteA or websiteB.

    I can easily find the count of distinct users with websiteA.
    I can easily find the count of distinct users with websiteB.
    Then I can do distinct for (websiteA or websiteB).
    Then I can do math to find out the union.
    But I'm wondering if there is one query that I can run instead of three.

IMN logo majestic logo threadwatch logo seochat tools logo