1

Am trying to get desired results from sql query by joining multiple tables. Since am having limited knowledge, seeking help from you all.

Am trying to get audit details per manager and employees. I have 3 tables:

1. HR
2. REQUIRED_AUDITS
3. SCORE_ENTRY

Below is the sample data for HR Table:

+----+---------------+------------------+
| id | manager_email |        VP        |
+----+---------------+------------------+
|  1 | [email protected]  | [email protected]     |
|  2 | [email protected] | [email protected] |
|  3 | [email protected] | [email protected]    |
|  4 | [email protected] | [email protected]    |
|  5 | [email protected]  | [email protected]      |
+----+---------------+------------------+

Below is the sample data for REQUIRED_AUDITS table:

+----+---------------+----------------+-----------------+----------------+
| ID | manager_email | employee_email | audits_required | audit_eligible |
+----+---------------+----------------+-----------------+----------------+
|  1 | [email protected]  | [email protected]   |               5 | Y              |
|  2 | [email protected] | [email protected] |               2 | Y              |
|  3 | [email protected] | [email protected]  |               7 | Y              |
|  4 | [email protected]  | [email protected] |               5 | Y              |
|  5 | [email protected] | [email protected]  |              25 | N              |
+----+---------------+----------------+-----------------+----------------+

Below is the sample data for SCORE_ENTRY table:

+----+---------------+----------------+-------+
| ID | manager_email | employee_email | Score |
+----+---------------+----------------+-------+
|  1 | [email protected] | [email protected] | 85.04 |
|  2 | [email protected] | [email protected]  |   100 |
|  3 | [email protected]  | [email protected] | 80.50 |
+----+---------------+----------------+-------+

So now I want to show number of audits required for each manager and how many completed and also percentage of completion.

+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+
| Manager(list from "HR" table) | Total Audits Required(Sum of audits required from "REQUIRED_AUDITS table") | Audits Performed(from "SCORE_ENTRY" table) | Percentage Completion |
+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+
| [email protected]                  |                                                                         10 |                                          1 | 10%                   |
| [email protected]                 |                                                                          9 |                                          2 | 22.22%                |
| [email protected]                 |                                                                          - |                                          - | -                     |
| [email protected]                 |                                                                          - |                                          - | -                     |
| [email protected]                  |                                                                          - |                                          - | -                     |
+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+

Calculations are as per below:
1. To calculate number of audits i.e., from REQUIRED_AUDITS table:
Considering manager [email protected]:
Total audits required: 7+3 because only two employees are eligible
2. Total audits completed:
audits_required (from REQUIRED AUDITS table) - audits completed (from SCORE_ENTRY table)

As said am having limited knowledge on sql and this look really complex for me and hence reaching out in Stack Overflow.

Appreciate any help.

UPDATE To further simplify the process, I wrote discrete queries excluding HR table and need help in combining below queries.

Query 1: I call this as "DENOMINATOR":

SELECT required_audits.manager_email, 
       SUM(audits_required) AS "TOTAL_AUDITS_REQUIRED" 
FROM   required_audits 
WHERE  Upper(required_audits.audit_eligible) = Upper('Y') 
GROUP  BY required_audits.manager_email 
ORDER  BY "total_audits_required" DESC 

+-------------------------------+-----------------------+
| required_audits.manager_email | TOTAL_AUDITS_REQUIRED |
+-------------------------------+-----------------------+
| [email protected]                  |                    10 |
| [email protected]                 |                     9 |
+-------------------------------+-----------------------+

Query 2: I call this as "NUMERATOR":

SELECT score_entry.manager_email, 
       Count(id) 
FROM   score_entry 
GROUP  BY score_entry.manager_email 

+---------------------------+-----------+
| score_entry.manager_email | Count(id) |
+---------------------------+-----------+
| [email protected]              |         1 |
| [email protected]             |         2 |
+---------------------------+-----------+

In the final or result, all I need is NUMERATOR**/**DENOMINATOR * 100 in percent. Am getting confused in joins and applying conditions in where clause.

+---------------+-----------------------+------------------+-----------------------+
|    Manager    | Total Audits Required | Audits Performed | Percentage Completion |
+---------------+-----------------------+------------------+-----------------------+
| [email protected]  |                    10 |                1 | 10%                   |
| [email protected] |                     9 |                2 | 22.22%                |
+---------------+-----------------------+------------------+-----------------------+

On result table, only audit eligible numbers should show up. This is another reason where am going wrong.

Thanks in advance.

Thanks,
Richa

Improve this question
2
  • Your original result set shows all managers in HR table. Your second result set shows only managers with staff in the REQUIRED_AUDITS table. We cannot provide a solution if you keep changing the requirement. Please edit your question to make it consistent throughout. Commented Feb 11, 2018 at 8:57
  • @APC. Thanks fro the feedback. I was trying to explain the same original OP in more simplified terms. I wasn't sure that would create confusion. I gave a thought for your suggestion on why to have HR table because am technically gathering information from REQUIRED_AUDITS table and SCORE_ENTRY table. Now am thinking I do need HR table because that has few more columns like first name, last name, phone number etc so felt it will be good to include HR table. To simplify the data I did not include those columns. If I need to use first name of manager then I will have to join HR table. Sorry again. Commented Feb 12, 2018 at 2:25

3 Answers 3

Reset to default
1

You may use multiple common table expressions, computing each separately and joining together - just for you to understand what's going on.

SQL Fiddle

Query:

WITH aud(manager_email,Total_audits) AS
  (SELECT manager_email,
    SUM (
    CASE
      WHEN audit_eligible = 'Y'
      THEN audits_required
    END )
  FROM REQUIRED_AUDITS
  GROUP BY manager_email
  ),  --Total_audits

  scores(manager_email,Audits_Performed) AS
  (SELECT manager_email,
    COUNT ( ID )
  FROM SCORE_ENTRY s
  GROUP BY manager_email
  )  --Audits_Performed

SELECT h.manager_email manager,
  a.Total_audits,
  s.Audits_Performed,
  100 * s.Audits_Performed / a.Total_audits percentage_complete
FROM HR h
LEFT OUTER JOIN aud a
ON h.manager_email = a.manager_email
LEFT OUTER JOIN scores s
ON h.manager_email = s.manager_email
ORDER BY 2 DESC NULLS LAST

Results:

|       MANAGER | TOTAL_AUDITS | AUDITS_PERFORMED | PERCENTAGE_COMPLETE |
|---------------|--------------|------------------|---------------------|
|  [email protected] |           10 |                1 |                  10 |
| [email protected] |            9 |                2 |   22.22222222222222 |
| [email protected] |       (null) |           (null) |              (null) |
|  [email protected] |       (null) |           (null) |              (null) |
| [email protected] |       (null) |           (null) |              (null) |
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks @Kaushik. This works fine in SQL Fiddle but when I run in my instance am getting error ORA-32040: recursive WITH clause must use a UNION ALL operation. This terminologies are all new to me. Sorry.
@Richa : That should not be the case, which version of Oracle are you using? It is also possible that while editing you may have modified something.
Thanks @Kaushik. Am using 11gR2 version. I might have modified something while editing. My bad
Thanks @Kaushik Nayak. The code provided by APC is working fine. I will try your code once again and let you know. Much appreciate your help and time.
Thanks @Kaushik. This query worked even after adding multiple identical rows in score entry.
1

Given the posted data there is no need to use HR table in this query as the manager_email is also on the required_audits table. In real life using a column like manager_email as the foreign would be a really bad idea. It should be the hr.id column.

Nevertheless I have included it in the query because you requested that.

The arithmetic is fairly straightforward. The complication is that you need to use left outer join to HR to REQUIRED_AUDITS because not all managers have employees who need auditing, and also to join to SCORE_ENTRY because not all employees have been audited. 

select hr.manager_email
       , sum(ra.audits_required) as tot_audits_required
       , count(se.score) as audits_performed
       , (count(se.score) / sum(ra.audits_required)) * 100 as pct_complete      
from hr
left outer join ( select * from  REQUIRED_AUDITS 
                  where audit_eligible = 'Y') ra 
    on ra.manager_email = hr.manager_email
left outer join SCORE_ENTRY se 
    on se.employee_email = ra.employee_email
group by hr.manager_email
order by hr.manager_email
/

Demo on Oracle LiveSQL.

"On result table, only audit eligible numbers should show up."

To implement this version of the requirements simply turn the left outer join on REQUIRED_AUDITS into an inner join.

Improve this answer

12 Comments

Thanks @APC. This is amazing. I tried adding one line of data to "SCORE_ENTRY" table ie., [email protected] who is not eligible for audit. When I do so, on the final output under column "TOT_AUDITS_REQUIRED", am getting value 34 instead of 9 for manager [email protected]. Can you please let me know how to modify the original query to incorporate this? The reason I used HR table is to show details at VP level just in case. Thanks in advance.
Thanks. I changed the table data in updated question as you suggested there's no need of HR table data. I was going with your suggestion. Sorry if it created confusion. I might have done something wrong when editing.
Thanks and I totally understand. Sorry for all the confusion. I really appreciate your time and help
I tried with cool mind and took more time to go thru each and every line of query. It's working like a charm. I also added validations on front end of system where not eligible employees are not added to score table. Thanks for the help and time. I really appreciate.
Can you please let me know how to round percent column? I tried adding another line round((count(se.score) / sum(ra.audits_required)) * 100,2) as percent ** and **round((count(se.score) / sum(ra.audits_required)),2) as other_method_percent and am getting error " ORA-00904: "RA"."AUDITS_REQUIRED": invalid identifier". Thanks
|
-1

assuming from your sample score table above has we are to look at the qty of the scored rows and not the score this should help

select 
RA.manager_email, 
sum(RA.audits_requiered) as ReqQty,
(select count(manager_email) from SCORE_ENTRY as SE where SE.manager_email = RA.manager_email) as Compleated 

from REQUIRED_AUDITS as RA
group by manager_email
where audit_eligable='Y'

with the score table I was unclear if that is what you meant.

Improve this answer

4 Comments

Thanks @Job Cluff. You are absolutely right. Its the quantity of scored rows not the score itself. When I executed the code, am getting error ORA-00907: missing right parenthesis. For me visually it looked fine but for some reason am getting this error.
select RA.manager_email, sum(RA.audits_requiered) as ReqQty, (select count(manager_email) from SCORE_ENTRY as SE where SE.manager_email = RA.manager_email) as Compleated from REQUIRED_AUDITS as RA where audits_eligable='Y' group by manager_email
When answering questions which are tagged [oracle] please be sure to use valid Oracle syntax, especially when the OP has identified themselves as a beginner. The ORA-00907 error is because your code uses as for table aliases which is not valid for Oracle.
sorry, I was just trying to help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.