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

Pls. do comments here in my query

VB Code:

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? :confused:
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 :blush:

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.

VB Code:

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

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

VB Code:

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.

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

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

Nothing to be sorry about. Its all good :)

Thanks a lot for correcting mine

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

ok...fine...thanks

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

Thanks, works fine :thumb: :wave: :afrog:

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

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

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

Thanks to you again...