Databases
StackOverflow2013
Postsdbo
POCan a MySQL query summarize time+user data into a time x user report?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan a MySQL query summarize time+user data into a time x user report?
    primarykey
    data
    text
    <p>I have a MySQL table with the following schema that records user activity:</p> <pre><code>TABLE Activity: id INT AUTOINCREMENT // the primary key ts TIMESTAMP // similar to DATETIME user INT // a unique ID for each user action INT // action code </code></pre> <p>Each time a user does an action, a row is added to this table. What I want to do now is generate a report summarizing total user activity per day. It would, for instance, look something like this:</p> <pre><code>Date | User 1 | User 2 | User 3 -------------------------------------- 2013-03-05 | 12 | 34 | 56 2013-03-06 | 78 | 87 | 65 2013-03-07 | 43 | 21 | 12 </code></pre> <p>In this example, user 1 did 78 actions on March 6, and 43 actions on March 7, and user 3 did 56 actions on March 5. At this point I don't care what kind of actions were done on a particular day, just the total number of all actions that were done by a particular user.</p> <p>Now writing a MySQL query to summarize entries in the activity table into days for a single user is relatively straightforward, and I could run such a query for each user in turn (though the days columns in each result set might not align perfectly). Is there a way to do this for all users automatically in one query?</p> <p>I think this might be a little weird because the number of columns in the result could vary depending on how many users were active in the date range of interest. In other words, the number of columns in the report might be less than the number of users in the database.</p> <p>Essentially, rather than having MySQL generate a one-dimensional array of records as its output, I want it to generate a two-dimensional array of values, where the labels for the rows and columns are derived from the activity table. Is this even possible?</p> <p>I suppose I could always just go through the raw data in the table and build up a proper two dimensional array myself (I'll be working in PHP), but I was wondering if MySQL could do that for me.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRandall Cook
    1. USRandall Cook
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCan a MySQL query summarize time+user data into a time x user report?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCan a MySQL query summarize time+user data into a time x user report?
      1. USG-Nugget
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload