ruedi ruedi - 4 months ago 10
SQL Question

Average entries per day in dataset

I have the following table

Name|Date |Activity
Ann |2016-01-01|Sleep
Ann |2016-01-01|Walk
Pete|2016-01-01|Sleep
Pete|2016-01-01|Walk
Pete|2016-01-01|Swim


This table goes on for the whole year and for more than 100 people. I want to get the average amount of activities for each person. Like

Ann |3.3,
Pete|4.2


that says that Ann did 3.3 activities per day in the dataset timerange.

What I tried yet is

Select count(Date), Name from table
group by date, name


But when I run this I get more than one entry for each name. Could anyone help me get this query done?

Answer

Using the following query:

  SELECT Name, [Date], COUNT(*) AS cnt
  FROM mytable
  GROUP BY Name, [Date]

you get the count of activities per day for each person. You can wrap the above in a subquery and apply AVG in order to get the average number of activities for each person:

SELECT Name, AVG(cnt*1.0)
FROM (
  SELECT Name, [Date], COUNT(*) AS cnt
  FROM mytable
  GROUP BY Name, [Date]) AS t
GROUP BY t.Name