Re: Oracle reporting in SQL PLUS

Melissa_at_induhouse.com wrote:

> I have an audit project that I am currently working on and I have to
> run distributions on multiple columns one of the questions I have is
> that in the following query how can I get a total of count(*) What I
> want to see is the count for each value and a total count of all
> values i.e. column REV_BANK_AVAIL_CR_306 heading
> 'REVOLVING_AVAIL_CREDIT' format $999,999,999
> select (REV_BANK_AVAIL_CR_306 - mod(REV_BANK_AVAIL_CR_306,25000)) as
> REV_BANK_AVAIL_CRDT_306,count(*)
> from prospect.resp_test_tb
> where source_file_id between '&&BEGGING_DATE' AND '&&END_DATE'
> and status_actual='R'
> group by (REV_BANK_AVAIL_CR_306 - mod(REV_BANK_AVAIL_CR_306,25000))
> order by (REV_BANK_AVAIL_CR_306 -
> mod(REV_BANK_AVAIL_CR_306,25000)) rev_avail_crdt
> count(*)-------------------- ---------------100,000
> 20200,000 30300,000 10 total count(*)
> 60 is there a way to do this in one query?
> Yes! use the formatting capabilities of SQL*Plus. Here's an example on
> the emp file:
>
> col sal head "Salary" for $9,999.00
> break on report
> compute sum of count on report
>
> select deptno, sum(sal), count(sal) count
> from emp
> group by deptno
> order by deptno
> /
>
> It's the BREAK ON REPORT that does the trick, in combination with
> COMPUTE.
> The alias count on count(sal) is needed to do the compute correctly
> (else it
> should read compute cum of count(sal) on report)
>
> The resulting output is:
> DEPTNO SUM(SAL) COUNT
> --------- --------- ---------
> 10 8750 3
> 20 10875 5
> 30 9400 6
> ---------
> sum 14
> My second questions is that I have the same queries that I have to
> run on 2 different tables I want to see the counts on both is there a
> way to display this side by side? WITH OUT CUTTING AND PASTING INTO A
> SPREADSHEETi.e.TABLE ONE TABLE
> 2rev_avail_crdt count(*) rev_avail_crdt
> count(*)-------------------- ---------------
> ----------------- ------------100,000
> 20 100,000 20200,000
> 30 200,000 30300,000
> 10 300,000 10 total
> count(*) 60 total count(*)
> 60 THANKS IN ADVANCEDAN
Not in SQL*Plus: you'll get a carthesian product, messing up your sums. Try PL/SQL instead

--
Met vriendelijke groet,
kind regards,

Frank van Bortel
Technical consultant Oracle
V&L Informatica BV

Work                           Home
Postbus 545                    Hunzestraat 4
7500 AM Enschede               7555 WB Hengelo
(31)53.434.1500                (31)74.242.5046