## Archive for October 8th, 2009

## Oracle: nested SUM

From **Stack Overflow**:

Suppose I have this table

A B C D Datatypes are not important.

I want to do this:

SELECT a AS a1, b AS b1, c AS c1, ( SELECT SUM(d) FROM source WHERE a = a1 AND b = b1 ) AS total FROM source GROUP BY a, b, c, but I can't find a way (

SQL Developerkeeps complaining with.)`FROM`

clause not foundIs there a way? Is it possible?

This is of course possible if we just alias the query and prepend the alias to the field:

SELECT a, b, c, ( SELECT SUM(d) FROM source si WHERE si.a = so.a AND si.b = so.b CONNECT BY 16 >= level ) FROM source so GROUP BY a, b, c

This works well on this sample set of data:

WITH source AS ( SELECT FLOOR(MOD(level - 1, 8) / 4) + 1 AS a, FLOOR(MOD(level - 1, 4) / 2) + 1 AS b, MOD(level - 1, 2) + 1 AS c, level AS d FROM dual CONNECT BY 16 >= level ) SELECT a, b, c, ( SELECT SUM(d) FROM source si WHERE si.a = so.a AND si.b = so.b ) FROM source so GROUP BY a, b, c

, but it needs to reevaluate the subquery for each group.

In **Oracle**, there is a better way to do this query: nesting an aggregate `SUM`

inside an analytical `SUM`

.

Read the rest of this entry »