Nested aggregate function

[hoos1]

I am trying to write a SQL statement that returns a record with the minimum count value for a table<br><br>Select student_no, count(*)<br>&nbsp;from class<br>&nbsp;group by student_no<br>&nbsp;having count(*) = <br><br>(Select min(count(*))<br>&nbsp;&nbsp;from class<br>&nbsp;&nbsp;group by student_no);<br><br>Access doesn't seem to like the aggregate nested within the aggregate but I think this is valid SQL in other systems.&nbsp;&nbsp;Any ideas on how to do this?&nbsp;&nbsp;Thanks.<br><br>

 

[jimmythegeek]

Why would you use: min(count(*))?<br><br>The count function is always going to return a single value. Using <b>min</b> on a single value is unecessary.<br><br>Let's say this is what is returned by having the following syntax, which you have in the sub-select above: (the student_no would not be showing since it is not in the select, but it is shown here for clarification)<br><br>SELECT count(*) FROM class GROUP BY student_no<br><br>student_no&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Count<br>------------------------------------<br>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4<br>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br>12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3<br>13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4<br>14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br><br><br>What good is adding a <b>min</b> to this recordset going to get you? <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[DreewToo]

For the record with the lowest count of student_id's, I'd add this to Jim's code:<br><br>SELECT TOP 1 Count(*) AS Expr1<br>FROM class<br>GROUP BY student_id<br>ORDER BY Count(*) ASC;<br><br>HTH,<br>Drew<br><br>

 

[hoos1]

Jim, <br><br>The count function with a group by is going to return a single value for each student, as you've displayed.&nbsp;&nbsp;I now want to find the student that has taken the minimum number of courses. BTW: My table consists of a two part primary key - student_no and class_no.<br><br>Drew,<br><br>Your solution does the trick.&nbsp;&nbsp;If I use the TOP predicate in the sub-select it actually returns the minimum count value(s) and I can select all students with a count(*) IN that set.&nbsp;&nbsp;Thanks a lot - I didn't know this existed.<br><br>As a follow up, any idea how to work around nesting the count(*) in an average function.&nbsp;&nbsp;If I wanted to find the average number of classes taken by a student, the following won't work:<br><br>Select student_no, avg(count(*))<br>From class<br>Group By student_no;<br><br>Any ideas would be appreciated.&nbsp;&nbsp;Thanks again for the min count tip.

 

[DreewToo]

Hiya Hoos1,<br><br>I'm happy to have helped.<br><br>I tried making this work in one query, but my sql isn't very strong this morning, all i wound up doing is crashing my laptop...<br><br>Anyhow, this is an easy task with 2 queries, one to count the classes/student, and the second, based on the first, to average the count per student.<br><br>#1 qry_Classes_Per_Student<br>SELECT GenInfo.StudentID, Count(GenInfo.ClassID) AS CountOfClassID<br>FROM GenInfo<br>GROUP BY GenInfo.StudentID;<br><br>#2 qry_Avg_Classes_Per_Student<br>SELECT Avg(qry_Classes_Per_Student.CountOfClasID) AS AvgOfCountOfClassID<br>FROM Geninfo;<br><br>It looks like the first query would prep you for a number of different class studies, from minimum, to average, etc...<br><br>I'd love to know how to simplify this into one query, and it seems totally doable.&nbsp;&nbsp;Perhaps using a recordset instead, and running select statements off that would be smoother, and have less objects sitting around.<br><br>Good luck,<br>Drew

 

[hoos1]

I had done it using two queries as well but my goal was to get it into one.&nbsp;&nbsp;Anyway, if you have any more ideas let me know.&nbsp;&nbsp;Thanks again for your help.

 

[DreewToo]

I found this while wandering the web the other day...maybe it'll help?&nbsp;&nbsp;Gives the basics of a sub-select query...<br><br>SELECT * FROM CompanyInvoices <br>WHERE CompanyInvoices.Invoice = <br>(SELECT Max(Invoice) FROM CompanyInvoices As C2 WHERE C2.Company = <br>CompanyInvoices.Company) <br><br>John Viescas, author <br>&quot;Running Microsoft Access 2000&quot; <br>&quot;SQL Queries for Mere Mortals&quot; <br><A HREF="

http://www.viescas.com/"

TARGET="_new">

http://www.viescas.com/</A>

<br>&quot;Ray&quot; &lt;<A HREF="mailto:Ray.CK@usa.com">Ray.CK@usa.com</A>&gt; wrote in message news:<A HREF="mailto:11usns8hugrou64tv8bvr3k8s6lvr5djar@4ax.com">11usns8hugrou64tv8bvr3k8s6lvr5djar@4ax.com</A>...<br><br>have a good weekend,<br>Drew