• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2775
  • Last Modified:

Oracle SQLPLUS query

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
Adam Leinss
Asked:
Adam Leinss
  • 4
  • 3
  • 2
  • +1
1 Solution
 
soraCommented:
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
 
soraCommented:
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
 
Bigfam5Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DrSQLCommented:
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
 
Adam LeinssSenior Desktop EngineerAuthor Commented:
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
 
DrSQLCommented:
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
 
Adam LeinssSenior Desktop EngineerAuthor Commented:
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
 
DrSQLCommented:
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
 
Adam LeinssSenior Desktop EngineerAuthor Commented:
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
 
DrSQLCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now