CODEWITHSUNDEEP
sqloracle-database
Richa
Richa

Reputation: 449

Oracle SQL - Query to calculate values from multiple tables

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

Upvotes: 1

Views: 1905

Answers (3)

APC
APC

Reputation: 146209

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.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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) |

Upvotes: 1

Jon Cluff
Jon Cluff

Reputation: 109

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.

Upvotes: -1

Related Questions