Simple SQL (hopefully!) Query Question
I hope someone can help me with my unsure steps to formulate the SQL query for the next problem.
I have a simple table that records the names of visitors and dates. The relationship is many for 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 third third column, which records the name of the exhibit with which the visitor interacted. 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 is the distribution of single and multiple visits, in other words, how many people visited only once, how many people visited on two separate days, how many on 3 separate days, etc.
Can anyone please help? Thank you waiting!
Frankie
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 as well as the total number of visits for that name and the total number of dates visited.
To get an average exhibit for a day, 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;
source to share
If you only want to count the total number of different visits, including multiple visits on the same date, you can use:
SELECT [Name],
COUNT(*) AS Count_Dates
FROM MyTable
GROUP BY [Name]
However, if you do not want to count multiple visits on the same date, you can 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 all people who visited x times per day. However, this will not display numbers for counts where 0 people have visited so many times, for example if no one has visited 8 times then there will be no row for Count_Dates = 8. If you want to display a complete list from 0-10, you can create a temporary table Count_Dates and insert values from 0-10 and 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
source to share