Query Help (Grouping Data)

Results 1 to 2 of 2

Thread: Query Help (Grouping Data)

  1. #1
    Join Date
    Dec 1969

    Default Query Help (Grouping Data)

    I believe I&#039;m grouping data together in an efficient and poorly designed method. I&#039;m currently not very good at dealing with data in more than one table, so I&#039;m going to ask for help.<BR><BR>Consider these two tables of data:<BR><BR>Table Students<BR>(Field) (Field)<BR>Counter Student<BR>1 Jimmy<BR>2 Johnny<BR>3 Jerry<BR>4 Sarah<BR>(Autonumber) (Text)<BR><BR>Table Grades<BR>(Field) (Field)<BR>StudentCounter Grades<BR>1 100<BR>1 90<BR>1 80<BR>2 100<BR>2 50<BR>4 92<BR>(Number) (Number)<BR><BR>I want to write a single query that will list only the students with grades and sort them from the highest grade average to lowest. The output for this set of data should be three records as follows:<BR><BR>(Field) (Field) (Field)<BR>Student Average Grade # of Grades<BR>Sarah 92 1<BR>Jimmy 90 3<BR>Johnny 75 2<BR>(Text) (Number) (Number)<BR><BR>I think the query would involve an OUTER JOIN, but as I mentioned, I don&#039;t have a lot of experience working with joining tables.<BR><BR>So, what should the query be to give me the desired output?

  2. #2
    Join Date
    Dec 1969

    Default No OUTER JOIN needed...

    You would use an OUTER JOIN if you wanted *all* the students listed, regardless of whether they had any grades yet or not.<BR><BR>This is just a simple INNER JOIN:<BR><BR>SELECT S.Student, AVG(G.Grades) AS Average, COUNT(G.Grades) AS NumGrades<BR>FROM Students AS S INNER JOIN Grades As G<BR>ON ( G.StudentCounter = S.Counter )<BR>GROUP BY S.Student<BR>ORDER BY S.Student<BR><BR>Actually, if you wanted *all* the students, even those with no grades, you&#039;d just change the word "INNER" in that to the word "LEFT" and it would then be an outer join and, I think, would work.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts