Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

compute the sum and count in sqlplus report

Posted on 2006-06-02
12
Medium Priority
?
9,620 Views
Last Modified: 2012-07-06
I need to write a sqlplus report to get the count(fieldname)/sum(total fields) *100 , here is the student record query..

SET VERIFY OFF
 
clear breaks ;
clear computes ;
 
column Title format a50
column Total_Students format a11
column Grade format a7
SET ROLE USR_role_repselect;
SET PAGESIZE 67;

 
break on "Title" skip 1 -
 on report skip 2
     compute Sum LABEL "Subject Total" of "Total Students" on "Title"
     compute Sum LABEL "Total Students" of "Total Students" on report
 
set feedback OFF
select z.ztitle        "Title",
         count(z.zpidm)  "Total Students",
         z.zgrade        "Grade"
         from  (select col1 zpidm, n.col2 ztitle,
                     col3 zgrade
                       from student n,grade g
                         where ..
                         and...
                     group by z.ztitle,z.zgrade) ;

output is as follows

1      321      25      A
2      321      10      B
3      321      10      C
4      321      4      W
5      321      12      A-
6      321      19      B+
7      321      8      B-

I want to add column
Row #      CRN      Total Students      Grade      
1      321      25      A      25%
2      321      10      B      10%
3      321      10      C      10%
4      321      4      W      4%
5      321      12      A-      12%
6      321      19      B+      19%
7      321      8      B-      8%
8      321      9      C+      9%
9      321      3      C-      3%
10      321      1      D+      1%
            101            100%
            100%            

grade % equal to in my first case 25/101*100 = 25%, how would I add this percentage column, I want to print the same way as showing % value.
0
Comment
Question by:mahjag
  • 6
  • 5
12 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16817838
0
 

Author Comment

by:mahjag
ID: 16817848
is this available only in 10g? I am in 8i database
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16817866
Why don't you use  z.zgrade||'%' ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

Expert Comment

by:paquicuba
ID: 16817958
MikeOM_DBA is right, RATIO_TO_REPORT is a good option. You should be able to use it in 8i. I thought the grades were evenly distributed within a 100%.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16818287
mahjag,

This is what MikeOM_DBA's is suggesting you:

 > SELECT NUM, ROUND(RATIO_TO_REPORT(NUM) OVER ()*100)||'%' PCT FROM TAB1;

       NUM  PCT
       ------ ----
        20   17%
        10   9%
        10   9%
         5   4%
         4   3%
        60   51%
         8   7%

7 rows selected.
0
 

Author Comment

by:mahjag
ID: 16819254
I tried the analytical function and got either 0% or 1%

select z.ztitle        "Title",
       count(z.zpidm)  "Total Students",
       z.zgrade        "Grade",
        ROUND(RATIO_TO_REPORT((count(z.zpidm))) OVER ()*100)||'%' PCT,
     from (select col1 zpidm, n.col2 ztitle,
                     col3 zgrade
                       from student n,grade g
                         where ..
                         and...
                     group by z.ztitle,z.zgrade) ;


I dont know what this analytical function does,
0
 

Author Comment

by:mahjag
ID: 16819331
I was not sure if I gave an example of what I want.. here is another example..

SQL> BREAK ON JOB SKIP 1
SQL> COMPUTE SUM LABEL 'TOTAL' OF SAL ON JOB SELECT JOB, ENAME, SAL
  2  FROM EMP
  3  WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN')
  4  ORDER BY JOB, SAL;


The following output results:

JOB       ENAME             SAL
--------- ---------- ----------
ANALYST   SCOTT            3000
          FORD             3000
*********            ----------
TOTAL                      6000

CLERK     SMITH             800
          JAMES             950
          ADAMS            1100
          MILLER           1300
*********            ----------
TOTAL                      4150

SALESMAN  WARD             1250
          MARTIN           1250
          TURNER           1500
          ALLEN            1600
*********            ----------
TOTAL                      5600

I want the following add the percentage( SAL/TOTAL * 100)  column output to the same Please advise how this can be done by using the TOTAL label. I know I can change the query instead of using the COMPUTE SUM but I don't want to do a SUM(SAL) in query.


JOB       ENAME             SAL   PERCENTAGE
--------- ---------- ----------   ----------------
ANALYST   SCOTT            3000      e.g ) =(3000/6000) * 100
          FORD             3000
*********            ----------
TOTAL                      6000

CLERK     SMITH             800
          JAMES             950
          ADAMS            1100
          MILLER           1300
*********            ----------
TOTAL                      4150

SALESMAN  WARD             1250
          MARTIN           1250
          TURNER           1500
          ALLEN            1600
*********            ----------
TOTAL                      5600



0
 

Author Comment

by:mahjag
ID: 16819530
This analytical function did not compute on break in total, it might have taken the grand total and divided it by grades, is there any update on this. Please
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16819966
Try this:

  1  SELECT JOB, ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER ( PARTITION BY JOB )*100)||'%' PCT
  2  FROM EMP
  3  WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN')
  4* ORDER BY JOB, SAL
SCOTT@PROD > /

JOB           ENAME             SAL     PCT
---------     ---------- ----------       -----------------------------------------
ANALYST   SCOTT    3000            50%
                FORD       3000            50%
*********            ----------
TOTAL                      6000

CLERK     SMITH             800       19%
          JAMES             950            23%
          ADAMS            1100          27%
          MILLER           1300           31%
*********            ----------
TOTAL                      4150

SALESMAN  WARD             1250  22%
          MARTIN           1250         22%
          TURNER           1500         27%
          ALLEN            1600           29%
*********            ----------
TOTAL                      5600


10 rows selected.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16819979
You can sum the percentages, but without the % sign:

COMPUTE SUM LABEL 'TOTAL' OF SAL, PCT ON JOB

SCOTT@PROD > SELECT JOB, ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER ( PARTITION BY JOB )*100) PCT
  2  FROM EMP
  3  WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN')
  4  ORDER BY JOB, SAL
  5  /

JOB       ENAME             SAL        PCT
--------- ---------- ---------- ----------
ANALYST   SCOTT            3000         50
          FORD             3000         50
*********            ---------- ----------
TOTAL                      6000        100

CLERK     SMITH             800         19
          JAMES             950         23
          ADAMS            1100         27
          MILLER           1300         31
*********            ---------- ----------
TOTAL                      4150        100

SALESMAN  WARD             1250         22
          MARTIN           1250         22
          TURNER           1500         27
          ALLEN            1600         29
*********            ---------- ----------
TOTAL                      5600        100


10 rows selected.
0
 

Author Comment

by:mahjag
ID: 16820501
But when I apply this to my example

select ROUND(RATIO_TO_REPORT(count(sal) OVER ( PARTITION BY job)*100)||'%' PCT

This gives oracle error "windows funciton not allowed here" error..
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 375 total points
ID: 16820576
>>> select ROUND(RATIO_TO_REPORT(count(sal) OVER ( PARTITION BY job)*100)||'%' PCT

You're missing one right parenthesis:

select ROUND(RATIO_TO_REPORT(count(sal)) OVER ( PARTITION BY job)*100)||'%' PCT

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question