CODEWITHSUNDEEP
mysqldatecountaverage
Le Ray
Le Ray

Reputation: 387

Averaging Count For Unique Dates Separately

I am trying to find out an average number of times a user enters the office. I have a similar scenerio to the one below for unique users that come to office. My database has data for everytime a user comes to an office.

Please note: Everyone has different names, since I'm not using their name but some sort of ID.

Date         Person   Action
01-01-2014   John     Enter
01-01-2014   lilly    Enter
01-01-2014   lilly    Enter
01-01-2014   bill     Enter
01-02-2014   bill     Enter
01-02-2014   lilly    Enter
01-02-2014   bill     Enter
01-02-2014   John     Enter
01-02-2014   John     Enter

I can do a query to give me average by grouping users together and running an avg on the count, but it only works if I specify a date in the query. And I'm trying to make a query that would run the average for each date separately and give me an average for each date.

SELECT AVG(sub.count1)
FROM (
SELECT COUNT(*) AS count1, Date AS date1, user  FROM table1 WHERE         
date='01-01-2014' GROUP BY user
) AS sub

What I want in result

Date         Avgcount
01-01-2014   1.33
01-02-2014   1.667

thank you for the help.

Upvotes: 0

Views: 60

Answers (2)

Ricardo Órfão
Ricardo Órfão

Reputation: 69

I think this is what you want, at least is the result that you want:

SELECT date1, AVG(sub.count1)
FROM (
SELECT COUNT(*) AS count1, Date AS date1, user  FROM table1
GROUP BY date1, user
) AS sub
GROUP BY date1

Upvotes: 1

Evan Volgas
Evan Volgas

Reputation: 2911

So you want the average number of entries per person, per day... yes? In other words the total number of entries divided by the total number of people making those entries, right?

= # entries / # people making those entries

Yes?

If so, the following should do the trick:

SELECT COUNT(*)/COUNT(DISTINCT person) 
FROM table1
GROUP BY date
ORDER BY date ASC;

The count(*) will show me the number of entries per day. If your table contains anything other than "Entry" actions, you'll need to add a where clause that says WHERE action = "Enter." But otherwise, we just take the total number of entries, divide by the distinct number of people making those entries, summarize it by the date, and order it in ASC order.

Upvotes: 2

Related Questions