I am trying to get the average of a column after doing a division grouped by weeks.

Here is my original table

First I want to SUM them with group by weeks, and divide the amount/div

Now to get the average of amount/div which is (11.25 7.3 7.77)/3 = 8.77

I just want to get the 8.77

Here is what I tried:

SELECT AVG(amount/ div) from mytable GROUP BY week

but I didn't get the desired result of 8.77

CodePudding user response：

Sample DB made.

http://sqlfiddle.com/#!9/6c7fa7/9

Used FLOOR to match your values 11.25, 7.3, 7.77, usual ROUND(...,2) got 1 cent difference.

CREATE TABLE stats (
  id INT PRIMARY KEY AUTO_INCREMENT,
  week INT,
  amount INT,
  divs INT
);

INSERT INTO stats ( week, amount, divs ) VALUES ( 5, 50, 5 );
INSERT INTO stats ( week, amount, divs ) VALUES ( 5, 40, 3 );
INSERT INTO stats ( week, amount, divs ) VALUES ( 4, 35, 3 );
INSERT INTO stats ( week, amount, divs ) VALUES ( 4, 60, 10 );
INSERT INTO stats ( week, amount, divs ) VALUES ( 6, 70, 9 );

SELECT ROUND(AVG(amount_divs), 2)
FROM (
  SELECT FLOOR(100* SUM(amount)/SUM(divs))/100 AS amount_divs
  FROM stats 
  GROUP BY week
) x

If you run following query

SELECT AVG(amount/divs)
FROM stats

It has different meaning, it is the average of all records (5 values), not the 3 after SUM