Home » Other » Training & Certification » Help with GROUP BY
Help with GROUP BY [message #290619] Sun, 30 December 2007 18:15 Go to next message
sqlnoob
Messages: 2
Registered: December 2007
Junior Member
Hi,

I'm having some problems getting one query to work. It is for a university project and I haven't been taught to use SUM and GROUP BY but have been told they are quite simple to do.

The query is to list employee id's, names, titles and the total number of allocated hours for two of the employees. Allocated Hours are in the Assignment tables and the two employees are allocated to 2 projects.

e.g.
Amy Adams - Hours Allocated: 10
James Michaels - Hours Allocated: 8
Amy Adams - Hours Allocated: 13
James Michaels - Hours Allocated: 7

...so the query should display 23 for total hours allocated for Amy Adams and 15 hours allocated for James Michaels.

This is what I thought the answer was:
Select E.EmpID, E.Last_Name, E.First_Name, PR.PrjctID, PR.PTitle, 
       Sum(A.Allocated_Hours) As Total_Hours
From Projects PR, Assignments A, Employees E
Where PR.PrjctID = A.PrjctID
And A.EmpRef = E.EmpID
And (E.Last_Name = "Adams" Or E.Last_Name = "Michaels")
Group By E.EmpID


As I do not have Oracle SQL at home, I am testing this in SQL view of Microsoft Access. After entering the above commands and attempting to run the query, I get an error like this:
"You tried to execute a query that does not include the specified expression 'PTitle' as part of an aggregate function"

so I changed the last line to:
Group By E.EmpID, E.Last_Name, E.First_Name, PR.PrjctID, PR.PTitle


I'm not getting any errors now but 4 records are being displayed with different allocated hours (2 should be the same). I think I am doing something wrong with the SUM and GROUP BY lines.

Could someone please help me.

Thank You,

[Updated on: Mon, 31 December 2007 00:35] by Moderator

Report message to a moderator

Re: Help with GROUP BY [message #290620 is a reply to message #290619] Sun, 30 December 2007 19:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Without knowing what type of data you are running your query against, it's impossible to comment on your output.

This may help...SQL Tutorial.

By the way, if you would like to practice SQL and you don't have Oracle installed, you can request an account here and have instant access to practice SQL.
Re: Help with GROUP BY [message #290649 is a reply to message #290619] Mon, 31 December 2007 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you want the total per user you have to group per their id. This is correct.
But as you aggregate the data per user, you can no more have details such as projectid which is an "unaggregate" information.
Now, if we assume that "last name"+"first name" is unique, you can also aggregate per last_name and first_name and getting the same result.

Regards
Michel
Re: Help with GROUP BY [message #290654 is a reply to message #290619] Mon, 31 December 2007 01:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As
Group By E.EmpID, E.Last_Name, E.First_Name, PR.PrjctID, PR.PTitle
returned 4 records (instead of 2, as you'd like it to), let's see: e.empid, e.last_name and e.first_name are constants - or, at least, they should be. Every employee has its own (unique) ID, and one first and last name. If you can get rid of 'PR' columns from the query, I believe you'll get 2 records as an output. Because, it appeears that 'PR' columns are not unique here.

There are pr.prjctid and pr.ptitle (project ID and project title, I suppose; their combination is unique, right?). As every employee has 2 records, it means that they have worked on 2 different projects. So, if you GROUP BY 2 different projects, you can not expect to have them in only one record.

It also means that sample data should be modified in order to show which projects are those employees working on:
Amy Adams      - Hours Allocated: 10 -> OK, but which project is it?
James Michaels - Hours Allocated: 8  -> project?
Amy Adams      - Hours Allocated: 13 -> project?
James Michaels - Hours Allocated: 7  -> project?

This query will return total hours PER EMPLOYEE:
Select E.EmpID, E.Last_Name, E.First_Name
       Sum(A.Allocated_Hours) As Total_Hours
From Projects PR, Assignments A, Employees E
Where PR.PrjctID = A.PrjctID
  And A.EmpRef = E.EmpID
  And E.Last_Name IN ('Adams', 'Michaels')
Group By E.EmpID, E.Last_Name, E.First_Name;

If you need total hours per project, you'd use
Select PR.PrjctID, PR.PTitle, 
       Sum(A.Allocated_Hours) As Total_Hours
From Projects PR, Assignments A
Where PR.PrjctID = A.PrjctID
Group By PR.PrjctID, PR.PTitle;

However, combination of projects + employees returns as many records as number of unique combinations of employees and projects they work on.

There's also an option to do something like this: use your current (working) query as a data source (it is called an inline view) and extract information from it:
select x.empid, x.last_name, x.first_name, sum(x.total_hours) hours
from (Select E.EmpID, E.Last_Name, E.First_Name, PR.PrjctID, PR.PTitle, 
             Sum(A.Allocated_Hours) As Total_Hours
      From Projects PR, Assignments A, Employees E
      Where PR.PrjctID = A.PrjctID
        And A.EmpRef = E.EmpID
        And E.Last_Name in ('Adams', 'Michaels')
      Group By E.EmpID
     ) x
group by x.empid, x.last_name, x.first_name;

Finally, saying that you don't have an Oracle database to work on, you can download and install Oracle 10g Express Edition (XE). It is free and I believe that you *must* do it; learning Oracle on MS Access is ... well, not the right path.
Re: Help with GROUP BY [message #290676 is a reply to message #290619] Mon, 31 December 2007 01:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ Littlefool

You might have thought of suggesting like

select x.empid, x.last_name, x.first_name, sum(x.total_hours) hours
from (Select E.EmpID, E.Last_Name, E.First_Name, PR.PrjctID, PR.PTitle, 
             A.Allocated_Hours AS Total_Hours
      From Projects PR, Assignments A, Employees E
      Where PR.PrjctID = A.PrjctID
        And A.EmpRef = E.EmpID
        And E.Last_Name in ('Adams', 'Michaels')
     ) x
group by x.empid, x.last_name, x.first_name;


right ?

[ Your Actual inline view may not work ]

Thumbs Up
Rajuvan

[Updated on: Mon, 31 December 2007 01:44]

Report message to a moderator

Re: Help with GROUP BY [message #290685 is a reply to message #290676] Mon, 31 December 2007 01:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oops! Indeed ... my inline view would fail ORA-00937. I apologize, I wasn't paying enough attention and copy-pasted a wrong query.

I was, actually, thinking of
Group By E.EmpID, E.Last_Name, E.First_Name, PR.PrjctID, PR.PTitle
instead of
Group By E.EmpID
Re: Help with GROUP BY [message #290697 is a reply to message #290676] Mon, 31 December 2007 02:36 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
rajavu1 wrote on Mon, 31 December 2007 08:43

@ Littlefool

hehehe
Re: Help with GROUP BY [message #290700 is a reply to message #290619] Mon, 31 December 2007 02:55 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ooops .. Sorry Littlefoot . I apologize.

I didn't really mean to call you so . Smile

Thumbs Up
Rajuvan.

[Updated on: Mon, 31 December 2007 02:55]

Report message to a moderator

Re: Help with GROUP BY [message #290714 is a reply to message #290700] Mon, 31 December 2007 04:17 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL!!! I didn't even notice that! /forum/fa/2115/0/
Previous Topic: cant 'start' oracle
Next Topic: How to get the correct tree structure?
Goto Forum:
  


Current Time: Fri Mar 29 05:45:50 CDT 2024