CODEWITHSUNDEEP
mysql
Andy B
Andy B

Reputation: 205

how to get statistics for grouped data

I have a table of customer visits to a business with a column for date, and columns for customer information. 

+------+---------------------+------+
| id   | checkin             | name | ...
+------+---------------------+------+
|    1 | 2010-01-01 00:12:00 | Joe  | 
|    2 | 2010-01-01 00:18:00 | John |
|    3 | 2010-01-01 00:22:00 | Jane |
...

I want to get statistics by day of the week i.e. average, minimum, maximum visits listed by day of week. Something like:

+---------+---------+---------+
| day     | average | minimum | ...
+---------+---------+---------+
| Monday  | 45      | 30      | 
| Tuesday | 60      | 35      |
...

I believe I need to use a subquery or join in order to do this, but can't figure out how.

This query will total the visits for each day

SELECT dayname(checkin) as day, count(*) as total
FROM customer_log
group by date(checkin)

and then on the above result I need to run something like

SELECT sum(total), max(total), min(total)
from {above table}
group by day.

Thanks!

Upvotes: 1

Views: 98

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

Try this out:

SELECT dayname(checkinDate) day,
  sum(visitsPerDay) totalVisits,
  avg(visitsPerDay) avgVisits,
  max(visitsPerDay) maxVisits,
  min(visitsPerDay) minVisits
FROM (
  SELECT date(checkin) checkinDate, count(*) visitsPerDay FROM Customer_log
  GROUP BY checkinDate
) AS visitsPerDaySub
GROUP BY dayofweek(checkinDate)

Upvotes: 1

Related Questions