Weighted average calculation in MySQL? Weighted average calculation in MySQL? database database

Weighted average calculation in MySQL?


Use:

SELECT SUM(x.num * x.gid) / SUM(x.cou)  FROM (SELECT i.gid,               COUNT(i.gid) AS num,               s.cou          FROM infor i     LEFT JOIN SIZE s ON s.gid = i.gid         WHERE i.id = 4325      GROUP BY i.gid) x


You could place your original query as a sub-query and SUM the records. I could not test this as I don't have the dataset you do, but it should work in theory ;)

SELECT SUM(gid)/SUM(weights) AS calculated_average FROM (  SELECT gid, (COUNT(gid) * gid) AS weights  FROM infor   WHERE id = 4325   GROUP BY gid);