Page 1 of 1

Efficient SQL

Posted: Fri Jul 21, 2006 11:03 am
by Ree
I have been thinking about an efficient way of retrieving some data. Basically, I need to display a table like this:

Code: Select all

Mon Tue Wed Thu Fri Sat Sun Total Avg
User 1    -   -   -   -   -   -   -    -    -
User 2    -   -   -   -   -   -   -    -    -
User 3    -   -   -   -   -   -   -    -    -
User 4    -   -   -   -   -   -   -    -    -
User 5    -   -   -   -   -   -   -    -    -
Each cell will contain a number of logins of a certain user (for each week day, total and average). The login table that I need to generate this report from looks like this (LoginDate is an int, generated by PHP's time()):

Code: Select all

-----------------------
ID   UserID   LoginDate
1      2       1111111
2     30       1111111
3     95       1111111
-----------------------
So basically each record in the table is a login of a certain user for a certain date. Now I was wondering what would be an efficient query to generate the report?

Posted: Fri Jul 21, 2006 11:17 am
by feyd
an ORDER BY on User ID and the day of week (extracted using DATE_FORMAT() and FROM_UNIXTIME()) .. a COUNT() might be of use too, or a DISTINCT, depending on how you want information to be aggregated.

Posted: Fri Jul 21, 2006 11:26 am
by Ree
Would you mind providing the complete query?

Posted: Fri Jul 21, 2006 11:32 am
by feyd
I see no reason to do so, nor could I, if I wanted.

Posted: Fri Jul 21, 2006 11:35 am
by Ree
Sorry, didn't get you? You mean, you don't know how to write it?

Posted: Fri Jul 21, 2006 11:41 am
by John Cartwright
Ree wrote:Would you mind providing the complete query?
Were not here to do the work for you. Why don't you try, post back what kind of attempts you have made and then we'll move forward from there.

Posted: Fri Jul 21, 2006 11:50 am
by Ree
GROUP BY is easy of course, but COUNT(ID) + GROUP BY would only return the total count of logins for each learner. But how do I 'explode' the total login count to weekdays?