IT would depend on your SQL server, in Postgres/Oracle I'd use Window Functions. In MySQL... not possible afaik.
Perhaps you can fake it like this:
SELECT a.id, SUM(b.value) AS `sum`FROM test AS aJOIN test AS b ON a.`group` = b.`group`GROUP BY a.id, b.`group`;