Post reply

SQL Limitations?

  • June 10, 2008 at 12:37 pm

    #72173

    The below query works in sql 2k5 and in sql 2k gets error "Server: Msg 8120, Level 16, State 1, Line 1

    Column 'a.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Server: Msg 8120, Level 16, State 1, Line 1

    Column 'a.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

    select cstypeid,ctypeid,Pid,

    datavalue=

    case when pid in (196,198) then avg(Datavalue)

    else sum(datavalue) end,

    convert(datetime,convert(varchar(2),datepart(mm, [date])) + '/01/' + convert(varchar(4),datepart(yy, [date]))) as [date],

    datepart(mm, [date]) as [periodid],

    datepart(yy, [date]) as year,

    1 as id

    from

    Weekly_Hc_data_Daily as a

    where

    [date] between DATEADD(dd, -datepart(dd, '6/7/2008')+1, '6/7/2008')and dateadd(dd,-datepart(dd,dateadd(mm,1, '6/13/2008')),dateadd(mm,1, '6/13/2008'))

    group by

    cstypeid,ctypeid,pid,datepart(mm, [date]),datepart(yy, [date])

    Help me out

    -Perumal

  • June 10, 2008 at 1:07 pm

    #826921

    Please don't cross post. It wastes people's time and fragments replies. Many of us read all the forums.

    No replies to this thread please. Direct replies to

    http://www.sqlservercentral.com/Forums/Topic514754-8-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply