Re: SQL Query - Sum

You could create a view on the table which gets the total sum:

CREATE VIEW sum_test_table AS
SELECT sum(paid) sum_paid -- Must alias the column   FROM test_table;

Then join to your original query with a cartesian join:

SELECT t.paid,

       s.sum_paid
  FROM test_table t,

       sum_test_table s

-Peter

On Wed, 26 Apr 2000, Toshi Teruya wrote:

> Test table as follows:
>
> paid
> ----
> 25
> 25
> 50
> 50
>
> I would like to execute a query to return the following:
>
> paid total
> ---- -----
> 25 150
> 25 150
> 50 150
> 50 150
>
> The following query below works in MS Access:
>
> select paid, (select sum(t.paid) from test t) as total from test;
>
> But the query does not work in Oracle. An ORA-00936: missing expression
> occurs.
>
> I would prefer not to add a total column and run an update statement to
> sum since the table size will change. I would also prefer not to use a
> function. If anyone knows of any other way...
>
> Thanks in advance,
>
> Toshi
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Wed Apr 26 2000 - 00:00:00 CDT