Solved

Oracle SQLPLUS query

Posted on 2002-04-08
10
2,752 Views
Last Modified: 2011-10-03
Hello.

I'm trying to write a query wherein no new tables or views are created. I'm using Oracle 8i.  It is suppose to show for each project the weekly cost of each employee (salary is based on a 52 week year and 40 hour week) involved in that project.  In addition, it is supposed to show the weekly cost for each employee, a subtotal and average weekly salary for each project as well as grand total and overall average.

Well, this is my first time using SQL and I don't know how to sum up an Employee's hours on a certain project, and then return just ONE entry with all the hours worked for that project.  Here's what I have so far: http://stealth.kirenet.com/~aleinss/salary.sql.  If you can help me on getting the first part (computing the weekly cost of each employee for each project), I can probably use that query as a basis to solve the rest of the stuff.

Oh yeah, another question (probably simple), since I'm suppose to have a overall average and grand total, how can I put these after the table?

SQL> select table_name from all_tables;

EMPLOYEE                      
DEPARTMENT                    
DEPT_LOCATIONS                 PROJECT                        WORKS_ON                      
DEPENDENT                      

A description of each table is here:
http://stealth.kirenet.com/~aleinss/table_info.txt

Thanks!
Adam

0
Comment
Question by:Adam Leinss
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 5

Expert Comment

by:sora
ID: 6927332
Try this:

select FNAME, SUM(HOURS)
from PROJECT P, EMPLOYEE W, WORKS_ON W
WHERE P.SSN = W.ESSN
  AND W.PNO = D.PNUMBER
GROUP BY FNAME
order by PNAME ASC, LNAME ASC, FNAME ASC, MINIT ASC;


sora
0
 
LVL 5

Expert Comment

by:sora
ID: 6927350
sorry, try this instead:

select FNAME, SUM(HOURS)
from PROJECT P, EMPLOYEE W, WORKS_ON W
WHERE P.SSN = W.ESSN
 AND W.PNO = D.PNUMBER
GROUP BY FNAME
order by FNAME ASC;


sora
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 6927846
This will give you Projects, Employee, Weekly Salary,
Sub totals (weekly avg salary) and Grand total (avg salary)

SELECT DECODE(GROUPING(Pname), 1, 'All Projects',Pname) AS Pname,
       DECODE(GROUPING(ESSN), 1, 'Sub by Project', ESSN) AS ESSN,
       ROUND(AVG(salARY/52)) "Average Weekly Sal"
FROM project, WORKS_ON, employee
where pnumber = pno
  AND ESSN = SSN
GROUP BY ROLLUP (Pname, ESSN)
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6928226
aleinss,
   I like Bigfam5's use of the rollup, but I think the "this is my first time using SQL" would lead me to show you something that's a little less complex and easier for you to relate to. I recommend you try seperate selects first (makes debugging easier) and use a union to get it all in one:

select 'Project Employee Costs' section,
       PNAME Project,
       FNAME||' '||LNAME employee,
       Salary / 52 Weekly_Cost,
       Null Average_Cost
  from Project, Employee
 where exists (select 1 from works_on
                where pno=Projects.PNumber and essn=Employee.SSN)
union
select 'Project Employee Costs Project Summary',
       PNAME,
       'All',
       sum(Salary / 52),
       avg(Salary) / 52,
  from Project, Employee
 where exists (select 1 from works_on
                where pno=Projects.PNumber and essn=Employee.SSN)
 group by PNAME
union
select 'Project Employee Totals',
       'All',
       FNAME||' '||LNAME,
       Salary / 52,null
  from Employee
 where exists (select 1 from works_on
                where essn=Employee.SSN)
union
select 'Project Employee Costs Total',
       'All','All',',sum(Salary / 52),avg(Salary) / 52,
  from Employee
 where exists (select 1 from works_on
                where essn=Employee.SSN)
order by 1,2,3;

I cheated in the sequenceing by using titles that sort the data.  I could have used a code (1, 2, 3, or 4) for each section and handled the printing of a title in reporting logic (assuming I'll be using a report), but this is to illustrate a point more than show the ultimate.  But I wasn't clear on how HOURS in WORKS_ON would be used?

Once I had the data organized the way I wanted, then I could look at collapsing the sql or trying for performance.  But, while this is a lot longer than the other statements I think it's easier for you to work with and your data volumes would have to be pretty big before performance would be an issue.

You should also know that select #1 (Project/Emp) could generate most of what you want in SQL*Plus via the use of BREAK ON and COMPUTE commands.  Here's a link:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a82951/toc.htm#1001341

Good luck!
0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 6929256
sora:

I tried your query, and modified it, but it only gives
the total hours spent on each project and doesn't give any info on each separate employee within a project:

select FNAME, SUM(HOURS)
from PROJECT P, EMPLOYEE W, WORKS_ON W
WHERE P.PNUMBER = W.PNO
GROUP BY FNAME
order by FNAME ASC;

Closer to what I need, but not close enough...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:DrSQL
ID: 6929378
aleinss,
   I think I see how HOURS is used now.  Here's the revised multi-part sql (assuming salary is based on a 40 hour week):

select 'Project Employee Costs' section,
      PNAME Project,
      FNAME||' '||LNAME employee,
      Salary / (52*40) * Hours Weekly_Cost,
      Null Average_Cost
 from Project, Employee, Works_On
where Works_On.pno=Projects.PNumber and
      Works_On.essn=Employee.SSN
union
select 'Project Employee Costs Project Summary',
      PNAME,
      'All',
      sum(Salary / (52*40) * Hours),
      avg(Salary / (52*40) * Hours),
 from Project, Employee, Works_On
where Works_On.pno=Projects.PNumber and
      Works_On.essn=Employee.SSN
group by PNAME
union
select 'Project Employee Totals',
      'All',
      FNAME||' '||LNAME,
      Salary / 52,null
 from Employee
where exists (select 1 from works_on
               where essn=Employee.SSN)
union
select 'Project Employee Costs Total',
      'All','All',',sum(Salary / (52*40) * Hours), avg(Salary / (52*40) * Hours),
 from Employee, Works_On
where Works_On.essn=Employee.SSN)
order by 1,2,3;


ANd, if you just want to use SQL*Plus to handle your sub and grant totals:

break on section on Project page on report
compute sum label 'Weekly Total' avg label 'Weekly Average ' of Weekly_cost on Project
select 'Project Employee Costs' section,
      PNAME Project,
      FNAME||' '||LNAME employee,
      Salary / (52*40) * Hours Weekly_Cost,
      Null Average_Cost
 from Project, Employee, Works_On
where Works_On.pno=Projects.PNumber and
      Works_On.essn=Employee.SSN;

and then just print the Employee Summaries:
select 'Project Employee Totals',
      'All',
      FNAME||' '||LNAME,
      Salary / 52,null
 from Employee
where exists (select 1 from works_on
               where essn=Employee.SSN);

Good luck!

0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 6929468
Bigfam5: Hmm, interesting.  A bit above my head in terms of understanding how it works (it does seem to work).  May go back to it if I get the other solution working.

DrSQL: Alright, I like that name!  I'm trying your first query and it seems to be what I need.  I took out the section stuff as it seemed to be messing up the output.  Still plugging and chugging, but it looks like your solution is da best.  Will update later.

Thanks guys.
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 200 total points
ID: 6929561
aleinss,
   Just be aware that Bigfam5 is calculating the weekly salary and not using HOURS (just like I did in my first post).  However, the WORKS_ON is linked in and that column is available.  Also, I used the "section" column to sort my results and make it clear what each record meant.

Good luck!
0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 6929711
DrSQL: Thank for for all your help, I may need your help in the very near future!  Just one more question, is there a way of spacing out the output in my second query here:

PROJECT_NAME    TOTAL_COST AVERAGE_COST
--------------- ---------- ------------
Computerization    $753.85      $188.46
Newbenefits        $834.13      $278.04
...

so it's more "pleasing" to look at?

Here is the SQL code for the final total report:

column Total_Cost format $99999.99
column Average_Cost format $999.99

break on REPORT
compute sum label 'Grand_Total:' avg label 'Grand_Average:' of Total_cost on Report  

select PNAME,
     sum(Salary / (52*40) * Hours) Total_Cost,
     avg(Salary / (52*40) * Hours) Average_Cost
from Project, Employee, Works_On
where Works_On.pno=Project.PNumber and
     Works_On.essn=Employee.SSN
group by PNAME;

To everyone else: Thank you very much for your assistance
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6929828
aleinss,
   Glad to be of help.  If you need more, there are plenty here who can offer great advice - you've already "met" several on this question.

   As to the formatting - SQL*Plus isn't the best report tool, but:

set colsep "         "

should add some whitespace for you horizontally.  To skip lines between projects you can use

break on project skip 2

Good luck!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now