0

I'm struggling to get all the terms involved in a GROUP BY cleanly with MySQL. I currently use a query to bucket results based on month and year, then I create a comma separated list of values. The client is responsible for parsing the comma separated list.

This presents problems when the list gets too long and is truncated. It also appears messy, and I believe there might be a better way.

My query looks like:

  SELECT 
    DATE(date_format(time_of_creation, '%Y:%m:01 00:00:00')) t0,
    GROUP_CONCAT(TIMESTAMPDIFF(MINUTE, time_of_creation, t1))
  FROM rr
  GROUP BY
    YEAR(t0),
    MONTH(t0)
  ORDER BY t0;

Is there a way in to get the elements that fall into each bucket without returning a large comma separated list?

James Lam
  • 1,179
  • 1
  • 15
  • 21
  • What do you *want* the result to look like? You can easily change the parameter that sets the length of the `group_concat()` result. – Gordon Linoff Mar 24 '15 at 23:27

1 Answers1

1

So generate a separate row for each item, and give the client a large table:

SELECT DATE(date_format(time_of_creation, '%Y:%m:01 00:00:00')) as t0,
       TIMESTAMPDIFF(MINUTE, time_of_creation, t1) as value
FROM rr
ORDER BY to;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hey Gordon, thanks for the response. However, won't this return an arbitrary time diff as the TIMESTAMPDIFF is no longer an aggregate as mentioned in http://stackoverflow.com/questions/1591909/group-by-behavior-when-no-aggregate-functions-are-present-in-the-select-clause. – James Lam Mar 25 '15 at 00:48
  • As I understand the query, `time_of_creation` and `t1` are on each row so the diff will not be affected. (I removed the `group by`, which wasn't intended anyway). – Gordon Linoff Mar 25 '15 at 00:51