CODEWITHSUNDEEP
sqloracle-database
Tom
Tom

Reputation: 45104

SELECT SUM as field

Suppose i have this table

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 table where a=a1 and b=b1) as total
from table
group by a,b,c

...but I can't find a way (sqldeveloper keeps complaining with "from clause not found".)

Is there a way? Is it possible?

Upvotes: 5

Views: 21013

Answers (3)

Quassnoi
Quassnoi

Reputation: 425301

SELECT  a as a1,b as b1,c as c1,
        (
        SELECT  SUM(d)
        FROM    mytable mi
        WHERE   mi.a = mo.a
                AND mi.b= mo.b
        ) as total
FROM    mytable mo
GROUP BY
        a, b, c

It's much more simple and efficient to rewrite it as this:

SELECT  a AS a1, B AS b1, c AS c1, SUM(SUM(d)) OVER (PARTITION BY a, b) AS total
FROM    mytable
GROUP BY
        a, b, c

Note the SUM(SUM(d)) here.

The innermost SUM is the aggregate function. It calculates the SUM(d) a-b-c-wise.

The outermost SUM is the analytic function. It sums the precalculated SUM(d)'s a-b-wise, and returns the value along with each row.

Upvotes: 8

Maximilian Mayerl
Maximilian Mayerl

Reputation: 11359

Du you mean something like this?

select a as a1,
       b as b1,
       c as c1,
       sum(sum(d)) OVER (PARTITION BY a, b) AS total
from table
group by a,b,c

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

you can do it with aliases:

SELECT a AS a1, b AS b1, c AS c1,
       (SELECT SUM(d)
           FROM test_t t_in
          WHERE t_in.a = t.a
            AND t_in.b = t.b) AS total
  FROM test_t t
 GROUP BY a, b, c

Upvotes: 1

Related Questions