Calculations with group-wise sum and row count

Question

Is it possible to sort a table by a value, divided by the sum of values within each year, again divided by number of rows for each year? I've tried to illustrate it by this faulty pseudo-query:

select *, value / sum(value group by year) / count(value group by year) result from bar order by result desc;

A very simple table to show what the result should be.

+-------+-------+-------+---------------+
| title |  year | value | result        |
+-------+-------+-------+---------------+
| Hello |  2017 |  100  | 100 / 250 / 2 |
| Hello |  2017 |  150  | 150 / 250 / 2 |
| Hello |  2018 |  300  | 300 / 903 / 3 |
| Hello |  2018 |  301  | 301 / 903 / 3 |
| Hello |  2018 |  302  | 302 / 903 / 3 |
+-------+-------+-------+---------------+

I thought I could get there by creating dummy columns, but that just got an error message.

update bar set groupsum = sum(value) group by year;
update bar set groupcount = count(value) group by year;

Does anyone have a solution, preferably a short and sweet one? (I'm using MySQL if it matters.)

Answer 1

Please Alter you column year to col_year and value to col_value because YEAR is a builtin function of mysql. and run below query. I also attached screenshot of query and result. Thanks

 SELECT title, col_year, col_value, (col_value/total_sum/total_row) AS  result FROM bar 
LEFT JOIN 
(SELECT SUM(col_value) AS total_sum, COUNT(col_value) AS total_row,col_year AS qq FROM bar GROUP BY col_year) AS sub_query 
ON sub_query.qq = bar.col_year ORDER BY col_value