compute the sum and count in sqlplus report

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.
mahjagAsked:
Who is Participating?
 
paquicubaCommented:
>>> 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
 
MikeOM_DBACommented:
0
 
mahjagAuthor Commented:
is this available only in 10g? I am in 8i database
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
paquicubaCommented:
Why don't you use  z.zgrade||'%' ?
0
 
paquicubaCommented:
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
 
paquicubaCommented:
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
 
mahjagAuthor Commented:
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
 
mahjagAuthor Commented:
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
 
mahjagAuthor Commented:
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
 
paquicubaCommented:
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
 
paquicubaCommented:
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
 
mahjagAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.