June 10, 2008 at 1:01 pm
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:14 pm
The following is what is making the query whine:
convert(datetime,convert(varchar(2),datepart(mm, [date])) + '/01/' + convert(varchar(4),datepart(yy, [date])))
You probably need to add [date] or that entire calc to your GROUP BY statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 10, 2008 at 1:52 pm
Hi,
Thanks, It works when i add the specified in group by. Can i know how it works in sql 2k5 with adding this specific value in group by.
-Perumal
June 10, 2008 at 1:56 pm
It's apparently smart enough to handle manipulations of elements already in the group by. I can't say I have a formal explanation, but if it detects the two datepart statements, it might be smart enough to figure out that it's handled through the GROUP BY.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply