Re: Average number of rows

From: Onismus Radebe <onismusrATabsaDOTcoDOTza_at_za>
Date: Wed, 12 Nov 2003 13:29:59 +0200
Message-ID: <3fb219bb$0$64719@hades.is.co.za>

"Peter Amberg" <son42uk_at_yahoo.co.uk> wrote in message news:f8232d10.0311120247.3da5d273_at_posting.google.com...
> I have a table T with a column C. The values of C are not distinct,
> ie. there are several rows with the same value of C. I want to
> calculate the average number of rows with a distinct value of C. For
> instance,
>
> Row | C
> ----+---
> 1 | 1
> 2 | 1
> 3 | 1
> 4 | 2
> 5 | 9
> 6 | 9
>
> The value I want to calculate is 6 rows / 3 distinct values = 2.

SQL> create table table_t (column_c number) ;

Table created.

SQL> insert into table_t values (1) ;

1 row created.

SQL> insert into table_t values (1) ;

1 row created.

SQL> insert into table_t values (1) ;

1 row created.

SQL> insert into table_t values (2) ;

1 row created.

SQL> insert into table_t values (9) ;

1 row created.

SQL> insert into table_t values (9) ;

1 row created.

SQL> select count(*) as "rows",
  2         count(distinct(column_c)) as "distinct",
  3         count(*)/count(distinct(column_c)) as "value"

  4 from table_t
  5 .
SQL> /       rows distinct value
---------- ---------- ----------

         6 3 2

SQL> Kind regards.

--                                    \|/
Onismus                               o o
__________________________________oOO_(_)_OOo______________________________
E-Business and Information Management  | Tel: +27 11 350 3414
Knowledge Discovery & Dissemination    | Fax: +27 11 350 8585
onismusr-AT-absa-DOT-co-DOT-za         | Cel: +27 83 591 5310
___________________________________________________________________________

Received on Wed Nov 12 2003 - 05:29:59 CST