Home » Other » Training & Certification » Help with GROUP BY
| Help with GROUP BY [message #290619] |
Sun, 30 December 2007 18:15  |
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 #290654 is a reply to message #290619] |
Mon, 31 December 2007 01:02  |
 |
Littlefoot
Messages: 21823
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
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 |
 |
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 ]
Rajuvan
[Updated on: Mon, 31 December 2007 01:44] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 20:40:46 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
