grouped sql query (mysql) - order by

Question

Lets's say i have a table sign_ins which has data like so: (the real table has 3.5 million rows)

+-----------+---------+------------------+
| school_id | user_id | date(created_at) |
+-----------+---------+------------------+
|         1 |       4 | 2009-04-20       |
|         1 |       4 | 2009-04-21       |
|         1 |       4 | 2009-05-06       |
|         1 |       5 | 2009-04-20       |
|         1 |       5 | 2009-06-26       |
|         1 |       5 | 2009-06-26       |
|         2 |       6 | 2009-04-21       |
|         2 |       6 | 2009-06-26       |
|         2 |       7 | 2009-04-20       |
|         2 |       7 | 2009-04-20       |
+-----------+---------+------------------+

created_at is a datetime field but i'm calling date() on it to get the day.

I have the concept of a "login_days" which is the number of distinct days on which a given user has a sign_in record. I want to order the schools by the number of login days, highest first, and return the number of login days.

So, looking at the data above, school 1 has two users (4 & 5). User 4 has three sign_ins, on 3 distinct days, so 3 "login_days". User 5 has three logins, but only 2 distinct days, so 2 "login_days". Therefore school 1 has 5 login days.

Looking at school 2, it has 3 login days: 2 from user 6 and 1 from user 7.

So, i would want to get this back from the query:

+-----------+------------+
| school_id | login_days |
+-----------+------------+
|         1 |       5    |
|         2 |       4    |
+-----------+------------+

I can't quite figure out how to do the query. I started off with this (i have the id < 11 part in there just to get my example data instead of my entire table of 3.5 million rows):

mysql> select school_id from sign_ins where id < 11 group by school_id, user_id, date(created_at);
+-----------+
| school_id |
+-----------+
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         2 |
|         2 |
|         2 |
+-----------+
8 rows in set (0.00 sec)

I can see in here that there are 5 rows for school 1 and 3 for school 2, which looks like it's worked. But i need to group that further, and order by that grouped number, to get it like in my required results. It must be something simple, can someone show me what i'm missing?

thanks, Max

Answer 1

MySQL allows you to count the number of distinct values for multiple expressions. So, this is basically an aggregation query with the appropriate count:

select school_id, count(distinct user_id, date(created_at)) as NumLoginDays
from sign_ins 
group by school_id;