sql divide column by column max

Question

I have a column of count and want to divide the column by max of this column to get the rate. I tried

select t.count/max(t.count)
from table t
group by t.count

but failed. I also tried the one without GROUP BY, still failed.

Order the count desc and pick the first one as dividend didn't work in my case. Consider I have different counts for product subcategory. For each product category, I want to divide the count of subcategory by the max of count in that category. I can't think of a way avoiding aggregate func.

Answer 1

If you want the MAX() per category you need a correlated subquery:

select t.count*1.0/(SELECT max(t.count) 
                    FROM table a
                    WHERE t.category = a.category)
from table t

Or you need to PARTITION BY your MAX()

select t.count/(max(t.count) over (PARTITION BY category))
from table t
group by t.count

Answer 2

The following works in all dialects of SQL:

select t.count/(select max(t.count) from t)
from table t
group by t.count;

Note that some versions of SQL do integer division, so the result will be either 0 or 1. You can fix this by multiplying by 1.0 or casting to a float.

Most versions of SQL also support:

select t.count/(max(t.count) over ())
from table t
group by t.count;

The same caveat applies about integer division.