# Calculations with group-wise sum and row count

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.)

• Which RDBMS are you using? MySQL/Postgres/etc? Commented Aug 12, 2018 at 12:54
• @Philᵀᴹ: Do you know how to solve the problem? Commented Aug 12, 2018 at 19:15

`````` SELECT title, col_year, col_value, (col_value/total_sum/total_row) AS  result FROM bar
• Yep, so basically you create a support table. There's no shortcuts other than this? Well, the query was not too slow for ~1M rows, and it's easy to speed it up by pre-calculating the support table. PS! It should have been `order by result desc`. PPS! MySQL now accepts column called `year` etc. I don't know when this was introduced. Commented Aug 13, 2018 at 6:00