PL-SQL averages, max and min

[Pits]

Hi,

I am an oracle newbie.

I need to calculate the averages, max and min number of rows in a table between two different dates.

How do I go about it?

Thanks.

Pits

 

[SantaMufasa]

Pits,

Here is sample code to do what you want:

col a heading "Average|Order $" format $999,999.99
col b heading "Maximum|Order $" format $99,999,999.99
col c heading "Minimum|Order $" format $999,999.99
col d heading "Number|of Orders" format 999
select avg(total) a, max(total) b, min(total) c, count(*) d
from s_ord
where date_ordered between to_date('01-AUG-92','dd-mon-rr') and to_date('30-SEP-92','dd-mon-rr')
/

     Average         Maximum      Minimum    Number
     Order $         Order $      Order $ of Orders
------------ --------------- ------------ ---------
 $129,905.75   $1,020,935.00      $377.00        16

1 row selected.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:09 (01Oct04) UTC (aka "GMT" and "Zulu"), 12:09 (01Oct04) Mountain Time)

 

[Pits]

Thanks.

But I don`t need to calculate avg, max and min values from the table . I need to calculate the average number of rows, max / min number of rows from a table which has many columns . I need to calculate the total number of rows, average number of rows, max/min number of rows between two different business days.

 

[SantaMufasa]

Pits,

I must presume that when you say, "I need to calculate the total number of rows, average number of rows, max/min number of rows between two different business days," that what you really mean is "I need to calculate the total number of rows, average number of rows, max/min number of rows per day. If my assessment is correct, then here is the adjusted code to achieve that result:

col a heading "Average|# Orders|Per Day" format 999
col b heading "Maximum|# Orders|Per Day" format 999
col c heading "Minimum|# Orders|Per Day" format 999
col d heading "Total|# Orders" format 999
select avg(cnt) a, max(cnt) b, min(cnt) c, sum(cnt) d
from (select count(*) cnt from s_ord group by date_ordered);

 Average  Maximum  Minimum
# Orders # Orders # Orders    Total
 Per Day  Per Day  Per Day # Orders
-------- -------- -------- --------
       2        5        1       16

1 row selected.

If my assumptions were incorrect, please correct me while offering a sample scenario.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:57 (01Oct04) UTC (aka "GMT" and "Zulu"), 12:57 (01Oct04) Mountain Time)

 

[Pits]

Thanks so much.

I am sorry that I am not being very clear.

I need to calculate the total number of rows, average number of rows, max / min number of rows from aaaa.bbbbb
for a period between 09/23/2004 to 09/30/2004.

The table bbbbb has a column "names"( with distinct names), I need to make all the above calculations for all distinct names for a period of 8 days ( and not per day)

Please let me know if you need any further information.

Thanx once again

 

[SantaMufasa]

Pits,

How is this adjustment?:

col a heading "Average|# Orders|Per Name" format 999
col b heading "Maximum|# Orders|Per Name" format 999
col c heading "Minimum|# Orders|Per Name" format 999
col d heading "Total|# Orders" format 999
col e heading "# of|Distinct|Names"
select avg(count(*)) a, max(count(*)) b, min(count(*)) c, sum(count(*)) d, count(*) e
 from pits
 where period between to_date('23-SEP-2004','dd-mon-yyyy') and
                      to_date('30-SEP-2004','dd-mon-yyyy')
 group by name
/

 Average  Maximum  Minimum                # of
# Orders # Orders # Orders    Total   Distinct
Per Name Per Name Per Name # Orders      Names
-------- -------- -------- -------- ----------
      48       72       24      144          3

1 row selected

Let me know if this is getting closer.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:04 (01Oct04) UTC (aka "GMT" and "Zulu"), 14:04 (01Oct04) Mountain Time)