Oracle-L: Re: SQL Question

From: Gary Lewis <glewis_at_HENGE.COM>
Date: Thu, 11 Jan 1996 16:40:45 -0700
Message-Id: <9601120000.AA02242@alice.jcc.com>

Iraida - Percentage distributions are possible in SQL but not with a single pass at the data. You must get your denominator (ie, grand-totals) in a first pass at the data and populate a temp table with this number. Then in a second pass at the data you can produce the type of report you wanted. The scripts below illustrate this.

As others have suggested in their postings, PL/SQL is a slicker choice because you don't need a temp table to hold the grand-totals. There are other possibilities too, but these involve client-side tools (eg, a stat package like SAS or an ad hoc query tool like Esperant) that can produce these kinds of reports (and many others).

If you're looking for more information about this, you might want to take a look at a book I wrote entitled *ORACLE Reporting* about ad hoc queries against Oracle databases. You can get a brochure by emailing me privately if you're interested.

Gary

  Gary Lewis
  Komenda Publishing Company
  glewis_at_henge.com
  (303)543-8540

SQL scripts

SQL> start &&query_dir.\pass1
SQL> delete from totals;

1 row deleted.

SQL> insert into totals (select sum(num_emp) from temp);

1 row created.

SQL> start &&query_dir.\pass2
SQL> break on report
SQL> compute sum of num_emp pct on report
SQL> column pct format 999.99
SQL> select
  2          dept,
  3          num_emp,
  4          round((num_emp/grand_total)*100,2) pct
  5  from
  6          totals,
  7          temp

8 /
ENTER to continue ...

DEPT NUM_EMP PCT
---------- ---------- -------

        10         60   28.57
        20         70   33.33
        30         80   38.10
           ---------- -------
sum               210  100.00

Received on Thu Jan 11 1996 - 19:00:31 CST