April 5th, 2013, 06:44 PM
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?
April 6th, 2013, 04:32 AM
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.
Tips on how to ask better questions:
April 8th, 2013, 11:44 AM
Here are the pertinent parts:
CREATE TABLE jnam.fcreg
website_name character varying (255),
create_time timestamp with time zone,
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.