Can I use the MAX function for each tuple in a dataset

Question

My result table contains fields:

id    count
____________
1     3
2     2
3     2

From this table I need to form another table score , which should look like this

id    my_score
_____________
1     1.0000
2     0.6667
3     0.6667

This my_score=count/MAX(count), but if I give a request like

create TEMPORARY TABLE(select id,(count/MAX(count)) AS my_score from result);

only the 1st row is retrieved.

Can anyone suggest a request so that my_score is computed for all tuples.

Thanks in advance.

Answer 1

SELECT
    a.ID,
    a.count / b.total
FROM result as A
   CROSS JOIN (SELECT MAX(Count) AS Total From Result) AS B

B returns only one row, so you want to take the Cartesian product of the table against its own aggregate to get the final value.

Answer 2

, mysql, :

select id, count / (select max(count) from result) as my_score
from result

Answer 3

, . - max , . , , .

create procedure calculate_score
begin
    declare maxcount decimal(6,4);

    set maxcount := select max(count) from result;

    select id, count / maxcount as score from result;
end

. , MySQL int . , .

Answer 4

- , :

select
    a.id
    , a.count / b.count
from
    result a cross join result b
where
    b.count = (select max(count) from result)

: @eftpotrm !