I need to make a job application report, I need to produce a table where the duplicated job_id and job_name will be removed from the table And I actually want to compute total applicants who apply for the job and then I need to get remaining job vacancies (no_of_vacancies - count()) and get the percentage by (count()/no_of_vacancies*100)

but I could only get this

The duplicated job id and name, no of vacancies and remaining vacancies is still there, and the percentage they shown is incorrect too Here's the code:

SET pagesize 30
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

ACCEPT v_jobID CHAR FORMAT 'A5' PROMPT ' Enter job id:  '

COLUMN job_id FORMAT A12 HEADING "Job ID"; 
COLUMN job_name FORMAT A20 HEADING "Job Name"; 
COLUMN applicant_id FORMAT A12 HEADING "Applicant ID"; 
COLUMN no_of_vacancies FORMAT 999 HEADING "No of Vacancies"; 
COLUMN Remaining_Job_Vacancies FORMAT 999 HEADING "Remaining Job Vacancies";
COLUMN Percentage FORMAT 999 HEADING "Percentage(%)";  

BREAK on J.job_id on job_name skip2 on no_of_vacancies, Remaining_Job_Vacancies, Percentage;
COMPUTE number LABEL 'Total Applicants' ON A.applicant_id;

TTITLE CENTER 'Job Application Report for ' _DATE -
RIGHT 'Page No: ' FORMAT 999 SQL.PNO SKIP 2

SELECT A.applicant_id, J.job_id, job_name, no_of_vacancies, (no_of_vacancies - count(*)) AS Remaining_Job_Vacancies, (count(*)/no_of_vacancies*100) AS Percentage
FROM applicant A, job J, application AP
WHERE A.applicant_id = AP.applicant_id 
AND AP.job_id = J.job_id 
AND J.job_id LIKE '&v_jobID'
GROUP BY A.applicant_id , J.job_id, job_name, no_of_vacancies
ORDER BY J.job_id;

--CLEAR COLUMNS
--TTITLE OFF

CodePudding user response：

That's because you don't break on table_alias.column_name

BREAK on J.job_id
         --
         ^
         |  
        this

but only on column name (or its alias):

BREAK on job_id

I don't have your tables nor data, so I'll use Scott's sample schema to illustrate it.

This is what you did:

SQL> break on e.deptno
SQL>
SQL> select e.deptno, e.ename from emp e order by e.deptno;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN

14 rows selected.

This is what you should have done:

SQL> break on deptno
SQL>
SQL> select e.deptno, e.ename from emp e order by e.deptno;

    DEPTNO ENAME
---------- ----------
        10 CLARK
           KING
           MILLER
        20 JONES
           FORD
           ADAMS
           SMITH
           SCOTT
        30 WARD
           TURNER
           ALLEN
        30 JAMES
           BLAKE
           MARTIN

14 rows selected.

SQL>