16

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?

Anonymous
  • 835
  • 1
  • 5
  • 21
Luca
  • 1,098
  • 1
  • 12
  • 19

2 Answers2

10

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`;
Wolph
  • 78,177
  • 11
  • 137
  • 148
1

No there isn't AFAIK. You will have to use a join like

SELECT t.`id`, tsum.sum AS `sum`
FROM `test` as t GROUP BY `group`
JOIN (SELECT `id`, SUM(`value`) AS `sum` FROM `test` GROUP BY `group`) AS tsum
     ON tsum.id = t.id
mattanja
  • 1,442
  • 14
  • 21
  • ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN (SELECT `id`, SUM(`value`) AS `sum` FROM `test` GROUP BY `group`) AS tsum ' at line 3 – just somebody Mar 04 '10 at 17:00