CODEWITHSUNDEEP
sqlmysql
kshama
kshama

Reputation: 1627

Can i use MAX function for each tuple in the retrieved data set

My table result contains fields: 

id    count
____________
1     3
2     2
3     2

From this table i have to form another table score which should look as follows

id    my_score
_____________
1     1.0000
2     0.6667
3     0.6667

That is my_score=count/MAX(count) but if i give the query as

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

only 1 st row is retrieved.

Can any one suggest the query so that my_score is calculated for all tuples.

Thanks in advance.

Upvotes: 3

Views: 343

Answers (4)

scherand
scherand

Reputation: 2358

I would be hesitating because of probable performance issues but semantically this should work:

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

Edit: @eftpotrm has a much more elegant solution!

Upvotes: 0

eftpotrm
eftpotrm

Reputation: 2281

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

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

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171529

Not sure if this works in mysql, but try:

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

Upvotes: 1

tvanfosson
tvanfosson

Reputation: 532605

I don't think you can apply an aggregation function to each row in one step. Use a stored procedure to do the calculation in two steps -- calculate the max and store it in a variable, then do your selection and divide the count by the variable. Alternatively, you could use a subquery, but I don't really see that as an improvement.

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

Note: I'm not sure if MySQL will implicitly handle the data conversions from int to decimal or what types your columns are. If the data conversions need to be handled manually, you'll have to adjust the above.

Upvotes: 0

Related Questions