Efficient SQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Efficient SQL

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

Would you mind providing the complete query?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I see no reason to do so, nor could I, if I wanted.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

Sorry, didn't get you? You mean, you don't know how to write it?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post 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?
Post Reply