Re: SQL Query - Sum

By the nature of the group by statement you are creating groups of paid items, hence loosing detail data. Therefore for each group (25 or 50 in this case) you will return exactly one row which provides the sum for each row. This statement will return:

paid sum(paid)

25     50
50     100

Which is not the result set Toshi is looking for. Perhaps you tested on a table with cardinality one?

-Peter

On Wed, 26 Apr 2000 michael_bialik_at_my-deja.com wrote:

> Hi.
>
> Why complicate things:
>
> SELECT paid, sum(paid ) FROM test
> GROUP BY paid;
>
> It worked for me.
>
> HTH. Michael.
>
> In article <8e75m0$bpp$1_at_nnrp1.deja.com>,
> Larry <lculver_at_my-deja.com> wrote:
> > Try this:
> >
> > select t.paid, tt.total
> > from test t, (select sum(paid) total from test) tt
> >
> > FYI the word "total" after the sum(paid) is an alias not a function.
> >
> > Larry
> >
> > In article <8e715f$6do$1_at_nnrp1.deja.com>,
> > Toshi Teruya <tteruya_at_sportsmail.com> 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.
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Wed Apr 26 2000 - 00:00:00 CDT