stupid sqlplus problem: compute both avg and sum

[bil...@my-deja.com]

How can I compute, using SQLPLUS, both the *sum* and the *average* of a
column?

This seems like pretty basic functionality, but I found:
1) I couldn't get both sums and averages to work on a column
2) the sqlplus manual says "if multiple COMPUTE commands reference the
same column in the ON clause, only the last COMPUTE command applies."

For the record, I tried:
compute sum label Total of nbr_logins on report
compute avg label Avg of nbr_logins avg_min_on_site
total_min_on_site on report

SQLPLUS would average the "nbr_logins" field, but not summarize it.

How can I get past this limitation?

thanks,

bill milbratz
chicago IL usa

Sent via Deja.com http://www.deja.com/
Before you buy.

[DanHW]

Only the last STATEMENT is applied, but the stament can have multiple
commands. Try

compute sum nbr_logins on report avg nbr_logins on report

(you will need to look it up for the precise format)

HTH
Dan Hekimian-Williams

[bil...@my-deja.com]

Dan,

Thanks for the suggestion. It makes sense, but unfortunately I haven't
gotten it or a variant to work.

> compute sum nbr_logins on report avg nbr_logins on report

Also, per your comment on the exact syntax, the syntax of COMPUTE, from
the SQLPlus reference, doesn't mention any "loop"--i.e. the ability to
specify more function:

COMP[UTE] [function LAB[EL] text ]
of {expr|column|alias}
on {expr|column|alias|REPORT|ROW}

any other suggestions?

bill milbratz

In article <20000827110635...@ng-mg1.aol.com>,

[Frank van Bortel]

compute sum of nbr_logins avg of nbr_logins on report

--
Kind Regards,
Frank
<bil...@my-deja.com> wrote in message news:8oe1g8$dc6$1...@nnrp1.deja.com...

[Mike Dwyer]

It isn't very elegant, but you could select nbr_logins twice and assign
different column aliases, sum one and average the other.

select nbr_logins n1, nbr_logins n2 ...

compute avg of n1 on report
compute sum of n2 on report

[bil...@my-deja.com]

Frank,

Are you sure this works?:

> compute sum of nbr_logins avg of nbr_logins on report

I tried using your syntax above; sqlplus didn't perform either the
summary or average functions.

bill

In article <8og3ob$amc$1...@porthos.nl.uu.net>,
"Frank van Bortel" <f.van....@vnl.nl> wrote:
>
> --
> Kind Regards,
> Frank

[Dagoberto Navea Alfaro]

create one function calculate_avgsum(list parameters....) into database and
..... then
Select calculate_avgsum(list parameters....) from sys.dual

this is callable in all tools oracle.

I hope this help you

Note : Sorry my English ..like Tarzan
--
Dagoberto Navea Alfaro
Ingeniero en Computación e Informática
Analista de Sistemas
Unidad de Informática y Comunicaciones
Universidad Arturo Prat
I quique - Chile
"Solo pido a Dios fortaleza para cambiar las cosas
que puedo, resignación para aquellas que no puedo
y sabiduria para diferenciarlas"

Mike Dwyer escribió en mensaje <2wQq5.2$sR3...@wdc-read-01.qwest.net>...

>It isn't very elegant, but you could select nbr_logins twice and assign
>different column aliases, sum one and average the other.
>
>select nbr_logins n1, nbr_logins n2 ...
>
>compute avg of n1 on report
>compute sum of n2 on report
>