0

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? – Philᵀᴹ Aug 12 '18 at 12:54
  • @Philᵀᴹ: Do you know how to solve the problem? – forthrin Aug 12 '18 at 19:15
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

enter image description here

  • 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. – forthrin Aug 13 '18 at 6:00
  • Yes, you can calculate inside subquery also. Thanks for mark as best answer. Happy to Help! – Mohit Kumar Aug 13 '18 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.