Search code examples
sqlmysqlgroup-by

How to apply a SUM operation without grouping the results in SQL?

I have a table like this one:

id group value
1 GROUP A 0.641028
2 GROUP B 0.946927
3 GROUP A 0.811552
4 GROUP C 0.216978
5 GROUP A 0.650232

If I perform the following query:

SELECT `id`, SUM(`value`) AS `sum` FROM `test` GROUP BY `group`;

I, obviously, get:

id sum
1 2.10281205177307
2 0.946927309036255
4 0.216977506875992

But I need a table like this one:

id sum
1 2.10281205177307
2 0.946927309036255
3 2.10281205177307
4 0.216977506875992
5 2.10281205177307

Where summed rows are explicitly repeated.

Is there a way to obtain this result without using multiple (nested) queries?

Solution

  • 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 a
JOIN test AS b ON a.`group` = b.`group`
GROUP BY a.id, b.`group`;