#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Overlapping GROUP BY


    I have a query that gets the number of events that happen on an object for each minute in a dynamically generated "time_intervals" table. So the results look something like this:

    object | timestamp | events

    NULL | 2012-10-01 00:00:00 | 0
    NULL | 2012-10-01 00:01:00 | 0
    object1 | 2012-10-01 00:02:00 | 5
    object2 | 2012-10-01 00:02:00 | 7
    object1 | 2012-10-01 00:03:00 | 2
    NULL | 2012-10-01 00:04:00 | 0

    ...etc. What I want to do is take that and get the average value for the "events" column per object, but also include rows with no events/objects, since those represent minutes in which nothing happened and thus should contribute a zero to the average. Seems like I would have to write some complicated GROUP BY clause that includes all rows with object = NULL in each group, but I'm lost as to how to do that.

    Any help would be appreciated.

    Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    ?

    So given that data set, what would the result set look like ? (show the arithmetic too)
    And what's the PRIMARY KEY on this table?
    Last edited by cafelatte; October 11th, 2012 at 04:48 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    Sorry, should have been more clear. This is the result of the query I perform by joining the temporary time_intervals table with the events and objects table. I want to then take that query and group the results by objects.id, but also include all results with no object in each group.

    So, the group for object1 would include all rows with object = NULL, as would the group for object2, object3, etc. This is so when calculating, say, the average number of events per minute, the zero-values are also calculated so the average is legitimate.

    Hope this makes sense.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Originally Posted by jraede
    Hope this makes sense.
    Not to me, but there are some pretty smart cookies around here so it might to them.
    Personally, I like clear, concise examples that can be easily replicated with 'copy and paste'.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    157
    Rep Power
    89
    object | timestamp | events

    NULL | 2012-10-01 00:00:00 | 0
    NULL | 2012-10-01 00:01:00 | 0
    object1 | 2012-10-01 00:02:00 | 5
    object2 | 2012-10-01 00:02:00 | 7
    object1 | 2012-10-01 00:03:00 | 2
    NULL | 2012-10-01 00:04:00 | 0



    This isn't tested but is it what you are after?

    Code:
    SELECT
    	object,
    	(SUM(events) / ts.minutes) avgEvents
    
    FROM
    	time_intervals,
    	(select ((max(timestamp) - min(timestamp)) / 60) minutes from time_intervals) ts
    GROUP BY
    	object
    Last edited by TASB; October 12th, 2012 at 08:05 AM.

IMN logo majestic logo threadwatch logo seochat tools logo