SQL Query — AVG for Occurrence Across Multiple Columns

I have a table like the following

ID_A	ID_B	Avg_Class_Size
1	2	16
3	4	10
2	3	8
2	4	9

Where ID_A and ID_B represent distinct student ID codes, and AVG_Class_Size represents the average class size of the classes shared between students A and B.

I would like to calculate the average of the “avg_class_size” for each student, regardless of whether they are student “A” or student “B”, with results like below:

ID	AVG
1	16
2	11
3	9
4	9.5

Is there a simple way to accomplish this with a SQL query?

Answer

Select with UNION ALL all the ids and averages of the students and aggregate:

SELECT ID, AVG(Avg_Class_Size) average
FROM (
  SELECT ID_A ID, Avg_Class_Size FROM tablename
  UNION ALL
  SELECT ID_B ID, Avg_Class_Size FROM tablename
) t
GROUP BY ID

See the demo.
Results:

ID	average
1	16
2	11
3	9
4	9.5