หาค่าเฉลี่ยคะแนนรวมหารด้วยจำนวน ID ที่ไม่ซ้ำใน MySQL

บทนำ

เมื่อต้องการหาค่าเฉลี่ยของผลรวมคะแนน (Sum of Scores) หารด้วยจำนวน ID ที่ไม่ซ้ำกัน (Distinct IDs) สามารถใช้ SUM() ร่วมกับ COUNT(DISTINCT ...)

ตัวอย่างการสร้างตารางและเพิ่มข้อมูล

mysql> CREATE TABLE DemoTable (
    -> Id INT,
    -> Score INT
    -> );
Query OK, 0 rows affected (0.95 sec)

mysql> INSERT INTO DemoTable VALUES(10, 90);
mysql> INSERT INTO DemoTable VALUES(10, 190);
mysql> INSERT INTO DemoTable VALUES(11, 230);
mysql> INSERT INTO DemoTable VALUES(11, 130);

แสดงข้อมูลทั้งหมด

mysql> SELECT * FROM DemoTable;

ผลลัพธ์

+------+-------+
| Id   | Score |
+------+-------+
| 10   | 90    |
| 10   | 190   |
| 11   | 230   |
| 11   | 130   |
+------+-------+
4 rows in set (0.00 sec)

คำสั่งหาค่าเฉลี่ย: ผลรวม Score / จำนวน ID ที่ไม่ซ้ำ

mysql> SELECT SUM(Score) / COUNT(DISTINCT Id) FROM DemoTable;

ผลลัพธ์

+---------------------------------+
| SUM(Score) / COUNT(DISTINCT Id) |
+---------------------------------+
| 320.0000                        |
+---------------------------------+
1 row in set (0.00 sec)

การคำนวณแบบขั้นตอน

ID ที่ไม่ซ้ำ: 10, 11 = 2 ค่า

ค่าเฉลี่ย = 640 / 2 = 320

กรณีศึกษา: คะแนนเฉลี่ยต่อคน (Student Average)

-- คะแนนเฉลี่ยต่อคน (แต่ละคนอาจมีหลายคะแนน)
SELECT Id, AVG(Score) AS AvgScore
FROM DemoTable
GROUP BY Id;

-- คะแนนเฉลี่ยรวมของทุกคน (Overall Average)
SELECT AVG(Score) FROM DemoTable;  -- = 160

-- ผลรวมคะแนนต่อคน แล้วหาค่าเฉลี่ยของผลรวม
SELECT AVG(TotalScore) FROM (
    SELECT Id, SUM(Score) AS TotalScore
    FROM DemoTable
    GROUP BY Id
) AS SubQuery;  -- = 320