Mysql query sum values into multiple rows

Question

I have this

id    value
-----!-----
 1      3
 2      3
 1      2
 1      1

i have tried using

SELECT id,sum(value) FROM table GROUP BY id

but it shows something different.

   id   ! value
  --------------   
   1        6
   2        3

And i want to add values based on their id's but keeping id's there

 id   ! value
--------------   
 1      6
 2      3
 1      6
 1      6

Please help Thank you

peterm · Accepted Answer · 2013-08-26 07:13:14Z

4

Are you looking for this?

SELECT t.id, q.value
  FROM Table1 t JOIN
(
  SELECT id, SUM(value) value
    FROM Table1
   GROUP BY id
) q ON t.id = q.id

Output:

| ID | VALUE |
|----|-------|
|  1 |     6 |
|  2 |     3 |
|  1 |     6 |
|  1 |     6 |

Here is SQLFiddle demo

answered Aug 26, 2013 at 7:13

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grisha Weintraub · Accepted Answer · 2013-08-26 07:13:21Z

1

The naive approach would be :

select id, (select sum(value) from tbl where id = t1.id) value
from tbl t1

answered Aug 26, 2013 at 7:13

Grisha Weintraub

7,9961 gold badge28 silver badges47 bronze badges