Skip to content
{ 2009 10 08 }

MySQL Help

I need some help with a query in MySQL. I can’t believe that no one has ever needed it before, but google is failing me.

I have a table with the following columns: ID | User | ...few more in here... | Timestamp

I need a query that returns data in the following way


User | Date1 | Date2 | Date3 | ... | Total
User1| 4 | 5 | 4 | ... | 13

i.e. There needs to be a total for each Date, with that date as the column header.

In case it helps make it any clearer, I have a similar query that takes entries from the last week and groups them by weekday:

select
       User as name,
       sum(if(dayofweek(Timestamp) = 1, 1, 0)) as sun,
       sum(if(dayofweek(Timestamp) = 2, 1, 0)) as mon,
       sum(if(dayofweek(Timestamp) = 3, 1, 0)) as tue,
       sum(if(dayofweek(Timestamp) = 4, 1, 0)) as wed,
       sum(if(dayofweek(Timestamp) = 5, 1, 0)) as thr,
       sum(if(dayofweek(Timestamp) = 6, 1, 0)) as fri,
       sum(if(dayofweek(Timestamp) = 7, 1, 0)) as sat,
       count(ID) as total
     from
       Table
     where
       date(Timestamp) >= date_sub(now(), INTERVAL 8 DAY) and
       Timestamp < date_format(now(), '%Y-%m-%d')
     group by
       User
     order by
       User

Now, this method is fine when you have a finite number of columns (days of the week), but I need it to be dynamic and take a couple of parameter (it's used in a php script) as the date range.

HELP?!?!

Posted by sheepeatingtaz on Thursday, October 8, 2009, at 4:01 pm. Filed under Computers. Follow any responses to this post with its comments RSS feed. You can post a comment or trackback from your blog.

{ 3 } Comments

  1. Alan Bell | October 8, 2009 at 4:16 pm | Permalink

    the result set doesn’t need to be horizontal. If you return the results grouped by date then by user you can walk down the results writing them out as you go.

  2. Nightwish | October 9, 2009 at 9:39 am | Permalink

    I looked into it a while back, there is no way to do it in a single query. SQL forces you to specify the number of columns on the SELECT, so you have to have two queries and dynamic SQL.
    The first query gets the second one’s columns, then you do the calculations per column as you do.

  3. sheepeatingtaz | October 10, 2009 at 7:11 am | Permalink

    I also posted to the Ubuntu-UK Mailing list, and Sean Miller brought back a completely valid point – do the date bit in the php…

    https://lists.ubuntu.com/archives/ubuntu-uk/2009-October/021034.html

    $query  = "SELECT User as name";
$dt = $sdate;
while ( strtotime($dt) < = strtotime($edate) ) {
      $dt_title = date("d/m",strtotime($dt));
      $query .= "SUM(if(date_format(Timestamp,'%Y-%m-%d') = '" . $dt . "', 1, 0)) as " . $dt_title . ",";
      $dt = date("y-m-d",strtotime("+1 days",strtotime($dt)));
}
$query .= "count(ID) as total ";
$query .= "WHERE Timestamp BETWEEN '" . $sdate . "' AND '" . $edate . "' ";
$query .= "GROUP BY User ";
$query .= "ORDER BY User";

    And with a bit of tweaking (I ended up taking out most of the strtotimes, passing the dates in as timestamps and formatting the timestamps when needed - it made the while loop more reliable), that did the job very nicely. Thanks Sean!

Post a Comment

Your email is never published nor shared. Required fields are marked *