Mysql – Calculations with group-wise sum and row count

group byMySQLsorting

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

Best Answer

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

Related Solutions

Mysql – 2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

I believe you are asking for the sum of "distinct" values of owgh for each value of mid and pid - a different "granularity" of data.

e.g. for mid = 3 then add 1.5 + 0.6 + 1.2 + 3.0 = 6.3; only count the 1.5 once instead of twice. However if your record ids for the 1.5 were for 2 different pid values, then you would want to count the 1.5 twice.

One way you could do it is instead of querying the "test" table directly, calculate some of the aggregates beforehand:

Select mid,
COUNT(distinct pid) as cpid,
SUM(nwgh) as totalnwgh,
SUM(owgh) as totowgh    -- I added this one
from 
    (select mid, pid, sum(distinct owgh) as owgh, sum(nwgh) as nwgh from test group by mid, pid) as A
GROUP BY mid

Mysql – Improving performance for sum and group by with large datasets

The big issue is "inflation-deflation" when you do JOIN + GROUP BY.

Think of what JOIN does -- it generates an intermediate table with teams * types/team * users/type/team * results/... That is, that table is much bigger than any of the original tables.

Then it does the GROUP BY do deflate that back to one row per team.

Usually the combination gives COUNTs and SUMs that are bigger than expected. (Did you check?) And it takes a long time.

So, the trick is to use subqueries to avoid any single query with both JOIN and GROUP BY in it. (The combo is ok if the JOIN is known to be 1:1, which seems to be the case for teams and types, but not the other JOINs.)

There are two ways to do such subqueries:

SELECT
        ( SELECT SUM() FROM ... WHERE ... ) -- correlated subquery; single result only
    FROM ...

The subquery is effectively a GROUP BY without saying it, but it boils down to a single value, thereby avoiding the "inflation".

and

SELECT ...
    FROM ( SELECT COUNT(), SUM(...) FROM ... GROUP BY ...
         ) AS x  -- tmp table for some of the data; no JOIN
    JOIN ...

The subquery "deflates" some of the data before getting to the JOIN.

Caution: Using multiple subqueries of the second kind is slow because of lack of indexes:

SELECT ...
    FROM ( SELECT ... ) a
    JOIN ( SELECT ... ) b ON ...

5.6 helps somewhat because it discovers and adds an index.

Best Answer

Related Solutions

Mysql – 2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

Mysql – Improving performance for sum and group by with large datasets

Related Question