Sum multiple rows

Question

So I have a table like this:

id    mod    n1    n2    n3
1     1      1     1
1     2            2
1     3                  3
2     1      1
2     2            2
3     1      1

And I want to sum up each value for all the rows for a specific id into a column call total, but I don't want to group the id together because they have different mod number. I want a result like this:

id    mod   total
1     1     7
1     2     7
1     3     7
2     1     3
2     2     3
3     1     1

I can't use group by because it will give me the total for just each individual rows. How do I achieve the result that I want?

Answer 1

You could do something like this:

SELECT `table`.`id`, `mod`, mySum
FROM `table` 
JOIN (SELECT `id`, SUM(n1) + SUM(n2) + SUM(n3) AS mySum
        FROM `table` GROUP BY `id`) as grpTable
ON `table`.`id` = `grpTable`.`id`

Not sure about the performance of this though...

Answer 2

Try:

select t.id, t1.mod, t.total
from tab t1
join (select id, sum( IFNULL(n1,0)+ IFNULL(n2,0)+ IFNULL(n3,0)) as total
      from tab 
      group by id) t on t.id=t1.id