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

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0

    Sum x, sum y for each hour


    Hi.

    I have a database with x, y and 'time' columns.

    I want to summarize the x and y values for each hour based on the 'time' column (which is in the 00:00 format)

    How would i do that in a select sentence (if adviceable to do it that way)

    Nicolai
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    select avg(x), avg(y), HOUR('time') as h from sumtable
    group by h;

    Worked fine, but what if i want every half hour??

    Nicolai
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,129
    Rep Power
    4274
    i'm just curious, but how did you manage to solve your first problem so quickly? some other forum perhaps?

    and what is the exact datatype of your 'time' column? please do a SHOW CREATE TABLE for your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    i'm just curious, but how did you manage to solve your first problem so quickly? some other forum perhaps?

    and what is the exact datatype of your 'time' column? please do a SHOW CREATE TABLE for your table
    I solved it my self after thinking a little.

    I was just too hung up in my old Basic way of thinking and thought i had to use some kind of step procdure in order to get the data out in the format i wanted to.

    But anyway, here is the create statement. i prop shouldm rename the date and time columns...

    CREATE TABLE `gps` (
    `date` date DEFAULT NULL,
    `time` time DEFAULT NULL,
    `ttf` int(11) DEFAULT NULL,
    `longitude` decimal(28,10) DEFAULT NULL,
    `latitude` decimal(28,10) DEFAULT NULL,
    `2D3D` varchar(50) DEFAULT NULL,
    `alt` int(11) DEFAULT NULL,
    `dop` decimal(28,10) DEFAULT NULL,
    `svs` int(11) DEFAULT NULL,
    `fom` int(11) DEFAULT NULL,
    `x` int(11) DEFAULT NULL,
    `y` int(11) DEFAULT NULL,
    `temp` int(11) DEFAULT NULL,
    `bat` int(11) DEFAULT NULL,
    `status` int(11) DEFAULT NULL,
    `scap` int(11) DEFAULT NULL,
    `gps` int(11) DEFAULT NULL,
    `gsm` int(11) DEFAULT NULL,
    `freq` decimal(28,10) DEFAULT NULL,
    KEY `Date` (`date`,`freq`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Nicolai
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,129
    Rep Power
    4274
    Originally Posted by vestlink
    i prop shouldm rename the date and time columns...
    actually, you probably should combine them into a single DATETIME column if you can

    is this table related to any other tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    actually, you probably should combine them into a single DATETIME column if you can

    is this table related to any other tables?
    No, it's one table.

    Would i concatenate the columns into one?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,129
    Rep Power
    4274
    Originally Posted by vestlink
    Would i concatenate the columns into one?
    not sure if concatenation is the right technique, you might have to use INTERVAL arithmetic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    not sure if concatenation is the right technique, you might have to use INTERVAL arithmetic
    Ok. i'll look into that.

    Thx for your advice

IMN logo majestic logo threadwatch logo seochat tools logo