CODEWITHSUNDEEP
sqloracle-database
user4441082
user4441082

Reputation: 371

With clause and inner join trick

I am using Oracle. I have table name works looks like:

Company-name   Employee-name   salary
   A1       Jim     122000
...

Table 2 manages looks like (manager manage themselves)

Employee-name  Manager-name
   Jim          John
   Kate         John
   John         John

I want to return the company with profit=Revenue-total salary, where Revenue= Employee's salary*3 (Here, only employee generate Revenue,while managers don't). Here is my code

with  Employee_count as(
Select a.company-name, b.employee-name as Guru
From works a
Inner join manages b
On a.employee-name=b.employee-name
Where b.employee-name<>b.manager-name
Group by a.company-name
)

Comput_revenue as (
select a2.company-name, SUM(3*a2.salary) as Revenue
from works a2
Inner join Employee_count b2
On a2.company-name=b2.company-name
Where a2.employee-name=b2.Guru
group by  a2.company-name),

select a3.company-name, b3.Revenue-SUM(a3.salary) as Profit
from works a3 inner join Comput_revenue b3
On a3.company-name=b3.company-name;

I have two questions here:

(1) Is the alias to table name local to each clause please? should I use a, a2, a3 or just using a and b are fine?

(2) If it's correct, it still looks too big. Any idea to simplify it please?

Upvotes: 1

Views: 3976

Answers (2)

Koshinae
Koshinae

Reputation: 2330

I hope, it is not mandatory to use the WITH clause, as I found an easier solution for you:

Sample data

Works

COMPANY EMPLOYEE_NAME SALARY
A1      Jim           122000
A1      Kate          132000
A1      John          88000
A2      Jack          96000
A2      Jason         134000
A2      Janet         138000

Manages

EMPLOYEE_NAME MANAGER_NAME
Jim           John
Kate          John
John          John
Jack          Janet
Jason         Janet
Janet         Janet

SQL

The query that implements your logic is:

SELECT
      COMPANY,
      SUM(
          DECODE(EMPLOYEE_NAME, MANAGER_NAME, 0, SALARY * 3) - SALARY
      ) AS PROFIT
FROM WORKS
INNER JOIN MANAGES USING(EMPLOYEE_NAME)
GROUP BY COMPANY
ORDER BY 2 DESC;

Results

COMPANY PROFIT
A1      420000
A2      322000

What/Why

  1. You stated that revenue is salary*3, and managers don't count. Hence we only have to add the salary*3 to the revenue, if the current row is not a manager.
  2. As profit is revenue-salaries, we substract the salary from all revenue rows unconditionally.
  3. Then we group it by company.

Your approach is good, but you started to focus on using the CTE (WITH clause) feature.

Upvotes: 2

jarlh
jarlh

Reputation: 44805

More or less ANSI SQL compliant. Just change dashes to underscores in the table and column names, and add a comma after first with clauses and remove after second. (Syntax verified by the SQL Validator http://developer.mimer.com/validator/parser200x/index.tml#parser)

Upvotes: 0

Related Questions