CODEWITHSUNDEEP
phpmysqlsqlsql-server
Rookie
Rookie

Reputation: 929

Average of averages in multiple tables

This is the first time I use this website, so I hope I use it correctly. I am very new to using PHP and MySql, so I apologize if this is a stupid question.

I want to create multiple tables that will hold data about multiple marching band performances. For instance, I have a table called "Attitude_Scores" with the following columns (id,student_id, performance_date, score). The other tables have the same columns. The other tables for example are "Posture_Scores", "Instep_Scores" etc.

I found on this website how to get average score for each student for each table:

SELECT student_id, AVG(score) FROM Attitude_Scores WHERE student_id = '1'

Basically, what I want to is to get the global average of the average of "Attitude_scores", the average of "Posture_Scores", and the average of "Instep_Scores" for example for student id= 1. Global average for student 1 = AVG((average attitude_scores + average posture_scores + average instep_score)/3). It's hard to explain. I hope it's somehow clear. Your help would be greatly appreciated.

Upvotes: 1

Views: 321

Answers (2)

Joseph B
Joseph B

Reputation: 5669

Try this query. It should get you the individual averages as well as the global average. The global average is calculated by multiplying the individual averages by the respective count of scores (weighted) and then averaging the totals.

EDIT:

Modified the query to calculate the Global Average based on (average attitude_scores + average posture_scores + average instep_score) / 3. The SQL Fiddle (see link at the end) contains both versions.

SELECT 
student_id,
MAX(CASE Test WHEN 'Attitude' THEN avg_score ELSE 0 END) as Attitude_Average,
MAX(CASE Test WHEN 'Instep' THEN avg_score ELSE 0 END) as Instep_Average,
MAX(CASE Test WHEN 'Posture' THEN avg_score ELSE 0 END) as Posture_Average,
SUM(avg_score) / 3 Global_Average
FROM
(
    SELECT 'Attitude' Test, student_id, COUNT(*) cnt_scores, AVG(score) as avg_score
    FROM Attitude_Scores
    GROUP BY student_id
    UNION ALL
    SELECT 'Instep', student_id, COUNT(*) cnt_scores, AVG(score) as avg_score
    FROM Instep_Scores
    GROUP BY student_id
    UNION ALL
    SELECT 'Posture', student_id, COUNT(*) cnt_scores, AVG(score) as avg_score
    FROM Posture_Scores
    GROUP BY student_id
) All_Scores
-- WHERE student_id = 1  Remove the comment if you want the data for only student_id = 1
GROUP BY student_id;

Here's a SQL Fiddle demo.

Upvotes: 0

thebignoob
thebignoob

Reputation: 471

Join the tables based on common key, group by student_id and use avg(what ever column you desire). Let me know if you need further help.

For example,

SELECT student_id,
       AVG(score),
       AVG(whatever column)
FROM Attitude_Scores AS a
JOIN tablex AS x ON x.common_key = a.common_key
GROUP BY student_id

Upvotes: 1

Related Questions