Complicated query with aggregate function 1

[PTinVT]

Hi, I have a very complicated query (to me anyway!) that includes an aggregate function. It works fine when I Select only the authorpub.medlineauthorname, but when I include authorpub.rank I get the following error: runtime error 2147217887 "You tried to execute a query that does not include the specified expression 'rank' as part of an aggregate function." Could someone please explain how I can accomplish this? Thank you. PT

"SELECT count(*), authorpub.medlineauthorname, author.rank From AuthorPub " _
& "INNER JOIN author ON authorpub.medlineauthorname = author.medlineauthorname " _
& "WHERE authorpub.MedlineID IN " _
& "(SELECT authorpub.MedlineID FROM AuthorPub WHERE authorpub.MedlineAuthorName = " _
& "'" & strSelectedAuthor & "') " _
& &quot;AND authorpub.MedlineAuthorName <> '&quot; & strSelectedAuthor & &quot;' &quot; _
& &quot;GROUP BY authorpub.MedlineAuthorName &quot; _
& &quot;ORDER BY count(*) desc;&quot;

 

[jrbarnett]

Hi,

Changes highlighted in bold to show up.

Basically every select field in a query which is not using an aggregate function (in this case the count (*)) must be included in a group by statement (this is the rank field not being used in an aggregate function).

&quot;SELECT count(*), authorpub.medlineauthorname, author.rank From AuthorPub &quot; _
& &quot;INNER JOIN author ON authorpub.medlineauthorname = author.medlineauthorname &quot; _
& &quot;WHERE authorpub.MedlineID IN &quot; _
& &quot;(SELECT authorpub.MedlineID FROM AuthorPub WHERE authorpub.MedlineAuthorName = &quot; _
& &quot;'&quot; & strSelectedAuthor & &quot;') &quot; _
& &quot;AND authorpub.MedlineAuthorName <> '&quot; & strSelectedAuthor & &quot;' &quot; _
& &quot;GROUP BY authorpub.MedlineAuthorName, author.rank &quot; _
& &quot;ORDER BY count(*) desc;&quot;

John

 

[PTinVT]

Thank you! That works perfectly... and thanks for the explanation, too. PT