[thelist] SQL Problem

rudy r937 at interlog.com
Sun May 6 19:04:59 CDT 2001

>And I want to get the average value for each day.

hi marios

wish i had sql/server to test this on

you need to group by the day part of the date, i.e. drop the time part

  select theday=convert(char(10),timestamp,102),avg(value)
    from yourtable
   group by theday

102 is the ansi format including century, yyyy.mm.dd

like i said, i'm not sure if this is going to work, i'd want to test to see
if you can actually group by the alias name

you could try declaring a view, that will work --

  create view dayonly (theday, value)
   as select convert(char(10),timestamp,102), value
     from yourtable


  select theday, avg(value)
     from dayonly
      group by theday

of course, you wouldn't want to use "value" as the name of that column, as
that's a reserved word

hope that helps


More information about the thelist mailing list