Average for each row in MYSQL

Question

I would like to ask you guys how to count the average to the results below:

My code looks as follow:

SELECT `milestone_id`, `status`, sum(value), 
    CASE WHEN (status !='done') THEN (value ='0') ELSE sum(value) END as val 
FROM project_has_tasks 
WHERE project_id='56' 
AND milestone_id !=0 
GROUP BY milestone_id ASC

Results:

milestone_id | status | sum(value) | val |
121            done     81           81
122            undone   25           0
123            done     64           64
124            done     23           23

What I wanna to do is to put there an average number (as avg) for each row so the results should looks like this:

milestone_id | status | sum(value) | val | avg |
121            done     81           81    81
122            undone   25           0     40,5
123            done     64           64    48,3
124            done     23           23    42

And so on The question - How to achieve the results like above?

PS:
The average for row 121 is from 81/1 = 81
The average for row 122 is from (81+0)/2 = 40,5
The average for row 123 is from (81+0+64)/3 = 48,3
The average for row 124 is from (81+0+64+23)/4 = 42

Your status column is neither in the group by clause, nor is an aggregate function applied to it. That makes your query invalid. Also I don't understand how your average is calculated. — fancyPants, Aug 24 '17 at 12:14

Menelaos · Answer 1 · 2017-08-24T14:01:13.987

Intro

What you need is to calculate a running average.

This could easily be done by modifying the approach for calculating a running sum:

See: https://stackoverflow.com/a/1290936/1688441

Example

Create a view:

create view temp as 
SELECT `milestone_id`, `status`, sum(value), 
    CASE WHEN (status !='done') THEN (value ='0') ELSE sum(value) END as val 
FROM project_has_tasks 
WHERE project_id='56' 
AND milestone_id !=0 
GROUP BY milestone_id ASC

Run the following code:

SET @runtot:=0;
SET @runcount:=0;
SELECT
  id,
  val,
  (@runtot := @runtot + val) AS runTotal,
  (@runcount := @runcount + 1) AS runCount,
  (@runtot / @runcount) AS runCount
FROM
  temp;

For Testing

Since we don't have your database and tables, could be tested using the following table instead of view:

create table temp(id int, val  int);

insert into temp(id, val) values (121, 81);
insert into temp(id, val) values (122, 0);
insert into temp(id, val) values (123, 64);
insert into temp(id, val) values (124, 23);

score 0 · Accepted Answer · edited Aug 24 '17 at 14:34

this is my first time posting so please forgive me if I've left out anything crucial. I've used @ variables in MySQL to achieve something like this before. Hopefully it will help you. I tested this using DB fiddle and since I don't really know how you data is structured I had to simplify a bit. Here is the sample table I created.

CREATE TABLE project_has_tasks (`milestone_id` int(6), `status` varchar(100), `value` int(6));

INSERT INTO project_has_tasks (`milestone_id`, `status`, `value`) VALUES (1, 'done', 81);
INSERT INTO project_has_tasks (`milestone_id`, `status`, `value`) VALUES (2, 'undone', 24);
INSERT INTO project_has_tasks (`milestone_id`, `status`, `value`) VALUES (3, 'done', 64);
INSERT INTO project_has_tasks (`milestone_id`, `status`, `value`) VALUES (4, 'done', 23);

And here is the query I came up with to generate a rolling average.

SELECT milestone_id
       , Status
       , value
       , CASE WHEN (status != 'done') 
              THEN 0 
              ELSE value END AS val
       , (@v_total + CASE WHEN (status != 'done') 
                          THEN 0 
                          ELSE value END) / @v_count AS avg
       , @v_total := @v_total + CASE WHEN (status !='done') 
                                     THEN 0 
                                     ELSE value END
       , @v_count := @v_count + 1
  FROM project_has_tasks, (SELECT @v_total := 0, @v_count := 1) vars;

Results:

milestone_id | status | value | val | avg    | @v_total... | @v_count...
1            | done   |  81   | 81  | 81     |   81        |  2
2            | undone |  24   |  0  | 40.5   |   81        |  3
3            | done   |  64   | 64  | 48.3...|  145        |  4
4            | done   |  23   | 23  | 42     |  168        |  5

Average for each row in MYSQL

2 Answers2

Intro

Example

For Testing