sql select statement with a group by

Question

I have data in 2 tables, and I want to create a report.

Table A:

• tableAID (primary key)
• name

Table B:

• tableBID (primary key)
• grade
• tableAID (foreign key, references Table A)

There is much more to both tables, but those are the relevant columns.

The query I want to run, conceptually, is this:

select TableA.name, avg(TableB.grade) where TableB.tableAID = TableA.tableAID

The problem of course is that I'm using an aggregate function (avg), and I can rewrite it like this:

select avg(grade), tableAID from TableB group by tableAID

but then I only get the ID of TableA, whereas I really need that name column which appears in TableA, not just the ID.

Is it possible to write a query to do this in one statement, or would I first need to execute the second query I listed, get the list of id's, then query each record in TableA for the name column... seems to me I'm missing something obvious here, but I'm (quite obviously) not an sql guru...

Answer 1

You can do this:

SELECT avg(b.grade), a.tableAID, a.name 
FROM TableA a 
     JOIN TableB b
       ON b.tableAID = a.tableAID
GROUP BY a.tableAID, a.name

Just adding it to the group will work fine in your case.

Answer 2

 SELECT AVG(TableB.grade), TableB.tableAID, TableA.Name 
      FROM TableA INNER JOIN TableB
      ON TableA.TableAID = TableB.TableAID
      GROUP BY TableA.tableAID, TableA.Name

Answer 3

Alternative answer:

SELECT AVG(b.grade), a.tableAID, MAX(a.name )
FROM TableA a 
     JOIN TableB b
       ON b.tableAID = a.tableAID
GROUP BY a.tableAID

Just to get you thinking.