MySQL Join SUM column on the whole table

Question

Hi all,

Let's say I had the following table:

id    num_votes    total_rating
-------------------------------
1     20           30
2     40           13
3     15           25

I want to join the SUM of all ids, let's say, onto the entire table so it looks like:

id    num_votes    total_rating    sum
--------------------------------------
1     20           30              6
2     40           13              6
3     15           25              6

I tried to do a LEFT JOIN on itself but I only get a 1 row result -- any thoughts?

Thanks!

Answer 1

SELECT  t.*, idsum
FROM    (
        SELECT  SUM(id) AS idsum
        FROM    mytable
        ) q,
        mytable t

Answer 2

SELECT id, num_votes, total_rating, (SELECT SUM(id) FROM `table`) AS sum FROM `table`

This is an inline select and they can be expensive. But it works here.