EXPLAIN EXTENDED

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 Developer keeps complaining with FROM clause not found.)

Is 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

 
View query details

, 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 »