[RESOLVED] - why i got an error here in my query?

[noielen]

Pls. do comments here in my query

rsbrand.Open "SELECT MARGIN, BRAND, SUM(ADAMOUNT) as adamount1 FROM INITIAL_QUOTE_DETAIL GROUP BY BRAND ORDER BY BRAND", conn, adOpenDynamic, adLockOptimistic

why i got an error?
Error occured: "You tried to execute a query that does not include the specified expression MARGIN as part of an aggregate function"

Any help really appreciated... Thanks in advance

[RobDog888]

When you perform a aggregrate operation in the select list you need to perform some kind of aggregrate function on all items or include them in a GROUP BY clause.

SELECT MARGIN, BRAND, SUM(ADAMOUNT) as adamount1 FROM INITIAL_QUOTE_DETAIL GROUP BY BRAND, [b]MARGIN[/b] ORDER BY BRAND

[ganeshmoorthy]

when you use group by command you have put all the queried fields in the group by section

rsbrand.Open "SELECT MARGIN, BRAND, SUM(ADAMOUNT) as adamount1 FROM INITIAL_QUOTE_DETAIL GROUP BY MARGIN, BRAND ORDER BY  BRAND", conn, adOpenDynamic, adLockOptimistic

and ofcourse the fields with aggregate functions need not to be

Edit : edited the statement.

[RobDog888]

But you forgot to put the MARGIN field in the GROUP BY clause. The ORDER BY clause doesnt matter.

[ganeshmoorthy]

Rob : I'm sorry for the copy, paster mistake..edited the post and now it should be correct

[RobDog888]

Nothing to be sorry about. Its all good

[ganeshmoorthy]

Thanks a lot for correcting mine

[RobDog888]

[color=navy]Still because you did get the explaination in your post correct. Just a copy/paste error. [/coor]

[ganeshmoorthy]

ok...fine...thanks

[noielen]

Thanks to you all... actually this is my first to use GROUP BY with 2 fields...

[noielen]

Thanks, works fine

[shakti5385]

Solution is
'1f rsbrand.State = 1 Then
' rsbrand.Close
'End If
'rsbrand.Open "select margin,brand,adamount form initial_quote_detail where sum(adamount)=' adamount1'GROUP BY BRAND ORDER BY BRAND", conn, adOpenDynamic, adLockOptimistic
'shakti5385@ yahoo.co.In

[ganeshmoorthy]

what is this shakthi5385, i couldnt understand what you are trying to post...

[noielen]

Thanks shakti53585, but the above posters already helped my prob... and this thread has been resolved by them..

Thanks to you again...