Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL Query - Sum
Both yours and Larry's suggestions work great. Another person replied to me and said that the SQL statement I originally posted works in Oracle 8.1.5. I am running Oracle 7.3.4 right now, so that's just another factor.
Toshi
In article <Pine.OSF.4.21.0004261219510.20879-100000_at_wpi.WPI.EDU>,
Peter Connolly <peter_at_wpi.edu> wrote:
> 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.
> >
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Apr 27 2000 - 00:00:00 CDT