| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Query
A variety of ways on 8.0.4 followed by a couple of more ways in 8i, and
8.1.6 and higher:
SQL> select a.summer, sum(b.summer)
2 from summer a, summer b
3 group by a.summer
4 /
SUMMER SUM(B.SUMMER)
---------- -------------
3 18
6 18
9 18
In-line views around officially since 7.2 can do it. So, here is another way that is basically the same as the above:
SQL> select summer, x.total
2 from summer,
3 (select sum(summer) total 4 from summer) x
SUMMER TOTAL
---------- ----------
3 18
6 18
9 18
It wasn't until 8i (I think, which release I'm not sure) that we could nest the select in the select, in which case you could code like:
1 select summer, (select sum(summer) from summer) total
2* from summer
SQL> /
SUMMER TOTAL
---------- ----------
3 18
6 18
9 18
And if on 8.1.6 or higher, you could just go ahead and use the analytical functions like this so you only make one pass on the table:
SQL> select summer, sum(summer) over ()
2 from summer
3 /
SUMMER SUM(SUMMER)OVER()
---------- -----------------
3 18
6 18
9 18
If on 8.1.6 or higher, I would go for the last approach.
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of אדר יחיאל
> Sent: Sunday, March 17, 2002 12:03 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL Query
>
>
> Lucky for you.
> Please share the answer with the people who read the question
> !!!!!!!!!!!!!!!!!!!
>
> Yechiel Adar, Mehish Computer Services
> adary_at_mehish.co.il
>
> > -----Original Message-----
> > From: RShankar1_at_CHN.COGNIZANT.COM
> [SMTP:RShankar1_at_CHN.COGNIZANT.COM]
> > Sent: Wed, March 13, 2002 12:29 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: SQL Query
> >
> > Hi Friends,
> > I found the answer . sorry for troubling you.
> >
> > Thanks
> > Shankar
> >
> > -----Original Message-----
> > Sent: Wednesday, March 13, 2002 2:54 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi Friends,
> > I just need a help in a SQL statement. There is a test
> table with one
> > number field say X.
> > I needed a sql statement which will show the value of x for each row and
> > the
> > sum of all X.
> > For ex , if 3 rows are there with values 3,6,9 then the output should be
> >
> > X Sum(X)
> > ------------------
> > 3 18
> > 6 18
> > 9 18 .
> >
> > I am using oracle version 8.0.4 where i am not able to execute the sql
> > statement
> > select x, (select sum(x) from test) from test
> > which is showing error as missing expression .
> >
> > Thanks in advance.
> >
> > Regards,
> > Shankar
> >
> > -----Original Message-----
> > Sent: Wednesday, March 13, 2002 1:58 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > I use Tim Onion's site which has a comprehensive listing of basic DBA
> > scripts and there is also a zip file to download the lot in one go.
> >
> > www.timonions.com
> >
> > John
> >
> > -----Original Message-----
> > <mailto:david.m.nguyen_at_xo.com> ]
> > Sent: 12 March 2002 22:54
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I try to find some scripts (PL/SQL package) to monitor my
> oracle database
> > such as tablespace, rollback segment, etc. Does someone know a website
> > that
> >
> > has this?
> >
> > Thanks,
> > David
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > <http://www.orafaq.com>
> > --
> > Author: Nguyen, David M
> > INET: david.m.nguyen_at_xo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
> >
> > =========================================================
> > This electronic message contains information from the mmO2 plc Group
> > which may be privileged or confidential. The information is
> intended to be
> >
> > for the use of the individual(s) or entity named above. If you
> are not the
> >
> > intended recipient be aware that any disclosure, copying,
> distribution or
> > use of the contents of this information is prohibited. If you have
> > received
> > this electronic message in error, please notify us by telephone
> or email
> > (to the numbers or address above) immediately.
> > =========================================================
> >
> >
> >
> > *** eSafe scanned this email for malicious content ***
> > *** IMPORTANT: Do not open attachments from unrecognized senders *** <<
> > File: InterScan_Disclaimer.txt >>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
> INET: adary_at_mehish.co.il
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: elkinsl_at_flash.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Sun Mar 17 2002 - 12:43:22 CST
 |
 |