Simple SQL (hopefully!) Query Question

Question

Hi I hope someone can help me with my faltering steps to formulate a SQL query for the following problem.

I have a simple table that records visitor names and dates. The relationship is many to many, in that for any given date there are many visitors, and for any given visitor there will be one or more dates (i.e. repeat visits). There is a complicating third column that records the name of the exhibit(s) the visitor interacted with. The data might look like this:

NAME     ART            DATE
Joe      Picture 1      23-1-09
Joe      Picture 2      23-1-09
Joe      Picture 3      23-1-09
Janet    Picture 2      23-1-09
Joe      Picture 2      31-2-09

I want to know what the distribution of single and multiple visits are, in other words, how many people only visited once, how many people visited on 2 separate days, how many on 3 separate days, and so on.

Can anyone help please? Thank you in anticipation!

Frankie

Answer 1

SELECT NAME, COUNT(ART) as num_exhibits, COUNT(DATE) as num_days FROM table GROUP BY NAME;

This will give you a table of each name along with the total number of visits for that name and the total number of dates visited.

To get an average exhibit per date you can do:

SELECT 
    NAME,
    COUNT(ART) as num_exhibits,
    COUNT(DATE) as num_days,
    (num_exhibits / num_days) as avg_exhibit_per_day
FROM table GROUP BY NAME;

Answer 2

If you only want to count the total number of distinct visits, including multiple visits on the same date, you could use:

SELECT [Name],
COUNT(*) AS Count_Dates
FROM MyTable
GROUP BY [Name]

However, if you don't want to count multiple visits on the same date, you could use the following:

SELECT [Name],
COUNT(*) AS Count_Dates
FROM
(
    SELECT DISTINCT [Name],
    [Date]
    FROM MyTable
) a
GROUP BY [Name]

This will show you the distribution of total people who visited x times per day. However, this will not display numbers for counts where 0 people visited that many times - for example, if nobody visited 8 times, then there won't be a row for Count_Dates = 8. If you did want to display a full list from 0-10 visits, you could create a temp table of Count_Dates and insert values from 0-10, then use it as part of the main query.

SELECT Count_Dates,
COUNT(*) AS Count_Visitors
FROM (SELECT [Name], COUNT(DISTINCT [Date]) AS Count_Dates FROM MyTable GROUP BY [Name]) a
GROUP BY Count_Dates
ORDER BY Count_Dates