How to find the relative ratio in rows in mysql

Question

I'm new to MySQL query, so please help me out of this problem, thanks!

I have a table:

query   url   num_clicks
-------------------------
qa      ua    20
qa      ub    30
qa      uc    50
qb      ud    10
qb      ue    90

I would like to calculate the relative ratio according to same query, like:

query   url   num_clicks  ratio
--------------------------------
qa      ua    20          0.2
qa      ub    30          0.3
qa      uc    50          0.5
qb      ud    10          0.1
qb      ue    90          0.9

How can I do it using one query? Thanks!

Answer 1

SELECT query,url,num_clicks,num_clicks/t.totalclicks AS ratio
FROM MyTable
INNER JOIN (
   SELECT
   query,SUM(num_clicks) AS totalclicks
   GROUP BY query
) AS t ON MyTable.query = t.query