The sum of several lines

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 each value for all rows for a specific identifier in the total number of column calls, but I do not want to group the identifier together because they have a different mod number. I want to get this result:

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

I cannot use a group because it will give me the total for only each individual row. How to achieve the result I want?

Answer 1

You can 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 performance 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 

Answer 3

 SELECT `id`, `mod`, (SUM(n1) + SUM(n2) + SUM(n3)) AS total FROM `table` GROUP BY `id`,`mod` 

Answer 4

The second answer was correct, all he needed was the right ifnull calls

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

Answer 5

This should work for you. Worked at Oracle. Check if you need to change keywords in mysql.

 SELECT x.id, x.mod, y.sum FROM table x, (SELECT id, sum(nvl(n1, 0) + nvl(n2, 0) + nvl(n3, 0)) sum FROM table GROUP BY id) y WHERE x.id = y.id;