Group By question

Ok I am having trouble creating a group by statement. Here is a example of what I am trying to do. I am using oracle 8.0.3

Table Department
ID
DESC Table People
ID
DEPARTMENT_ID
NAME Table Accounts
ID
PEOPLE_ID
MAX_DOLLARS Table Expenses
ID
ACCOUNT_ID
AMOUNT
DESC So Departments have people, people have multiple accounts, and accounts have multiple expenses

So for each distinct department I need to get the department.desc, people.name,
the sum of the accounts.max_dollars per person, and then the sum of each persons expenses.

So I would think I would need to do something like this

select distict department.desc,
people.name,
sum(accounts.max_dollars),
sum(expenses.amount)
from department,
people,
accounts,
expenses
where
department.id = people.department_id and people.id = accounts.people_id and
accounts.id = expenses.accounts_id
group by department.desc,people.name;

So it would list like

DEPT  Name     MAX_DOLLARS      EXPENSES
AC    Mike     20,000           10,000
AC    Joe      10,000           5,000
DE    Ben      5,000            5,000

from this type of data

DEPT Table
1 AC
2 DE

PERSON Table
1 1 Mike
2 1 Joe
3 2 Ben

ACCOUNT Table

1 1 10,000
2 1 10,000
3 2 10,000

EXPENSE Table

1 1 2,000
2 1 2,000
3 1 2,000
4 2 4,000
5 3 2,000
6 3 3,000
7 4 1,000
8 4 1,000
9 5 1,000

am I totaly lost, what do I need to do here?

Thank you in advance Received on Thu Jan 24 2002 - 01:48:26 CET