none
error 8120 state 16

    Question

  • Hi!

    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..

    Best regards

    Omar_aa 

    Tuesday, July 08, 2008 5:45 AM

Answers

  • 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:53 AM
    Moderator

All replies

  • 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:53 AM
    Moderator
  • Thanks alot..the group by command works fine.......But how shud i solce my second problem??Any Suggestions.......??

    Best Regards

    Omar_aa

    Tuesday, July 08, 2008 5:58 AM
  • 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:06 AM
    Moderator
  • i m using the following query:

    SELECT max(((ISNULL(fre))/ (ISNULL(rgtrtg)))/1.0) as t

    FROM rawcdfsdfs

    where bgdf='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........

    Best Regards

    Omar_aa

    Tuesday, July 08, 2008 6:18 AM
  • 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:25 AM
    Moderator
  •  

    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................

    Best Regards

    Omar_aa

    Tuesday, July 08, 2008 6:37 AM