CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate
Josh.F
Josh.F

Reputation: 3806

How can I apply arithmetic operations to aggregated columns in MySQL?

TL;DR

Is there a way to use aggregated results in arithmetic operations?

Details

I want to take two aggregated columns (SUM(..), COUNT(..)) and operate them together, eg:

-- doesn't work
SELECT 
    SUM(x) AS x, 
    COUNT(y) AS y, 
    (x / y) AS x_per_y -- Problem HERE
FROM
    my_tab
GROUP BY groupable_col;

That doesn't work, but I've found this does:

SELECT 
    SUM(x) AS x, 
    COUNT(y) AS y, 
    SUM(x) / COUNT(y) AS x_per_y -- notice the repeated aggregate
FROM
    my_tab
GROUP BY groupable_col;

But if I need many columns that operate on aggregates, it quickly becomes very repetitive, and I'm not sure how to tell whether or not MySQL can optimize so that I'm not calculating aggregates multiple times.

I've searched SO, for a while now, as well as asked some pros, and the best alternative I can come up with is nested selects, ~~which my db doesn't support.~~

EDIT: it did support them, I had been doing something wrong, and ruled out nested selects prematurely

Also, MySQL documentation seems to support it, but I can't get something like this to work (example at very bottom of link)

https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

Upvotes: 5

Views: 2530

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

One way is using subquery:

select x,
    y,
    x / y as x_per_y
from (
    select SUM(x) as x,
        COUNT(y) as y
    from my_tab
    group by groupable_col
    ) t

Also note that the value of count(y) can be zero (when all y are null). MySQL handles this case automatically and produce NULL in case the denominator is zero.

Some DBMSes throw divide by zero error in this case, which is usually handled by producing null in that case:

select x,
    y,
    case when y > 0 then x / y end as x_per_y
from (
    select SUM(x) as x,
        COUNT(y) as y
    from my_tab
    group by groupable_col
    ) t

Upvotes: 6

Related Questions