Tuesday, July 08, 2008 5:45 AM
I am using visual studio 2005 and sql server 2005. i am writing stored procedure to calculate maximum value whhch uses max function.But whenever i tried my stored procedure in sql server it always states the following error message:
Msg 8120, Level 16, State 1, Line 1
Column 'rawcounters.SEGMENT_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
secondly, i m also writing a query that should returns max value against a valid id.but the problem is dat i want to return a single value which should be the sum of the two values which are returned against a valid id. n i shud apply it to whole of my table against each unique id..
Tuesday, July 08, 2008 5:53 AMModerator
Well as the Error message suggesting, in select statement you are doing some aggregation (MAX of value) and may be you have not mentioned group by clause in the query.
Just add the group by SEGMENT_ID'
Tuesday, July 08, 2008 5:58 AM
Thanks alot..the group by command works fine.......But how shud i solce my second problem??Any Suggestions.......??
Tuesday, July 08, 2008 6:06 AMModerator
well your scenario is not clear.
Those Ids have only 2 values against it? If yes then just do SUM instead of MAX.
And if 1 ID have more than 2 values in that case which 2 values you want to add?
Tuesday, July 08, 2008 6:18 AM
i m using the following query:
SELECTmax(((ISNULL(fre))/ (ISNULL(rgtrtg)))/1.0) as t
wherebgdf='dfgsd792'and s_id='139' and datetime='01/06/2008 01:00:00'
this query returns one value.....
but i just want to get the sum of two values which are present for this search criteria................secondly want to get this applied to the whole of my db........
Tuesday, July 08, 2008 6:25 AMModerator
If I'm understanding it correctly....
if only 2 values are available then just use SUM instead of MAX.
and instead of putting ID in Where clause, remove that from where clause and just add "Group by id" clause in the query.
Tuesday, July 08, 2008 6:37 AM
i have just tried it but still i m geettting problem.....Actually i m having a composite key which depends upon four deifferent entities.... so if i remove the where caluse and just add group by clause than it will give me a single value which is not the anser bcaz for a particulaar day i m getting 44 values and i have to select the max value and than add the max value with another value (the second value is also returned against this search criteria) and get the rsults for approx one million values................