Re: Group By question
Date: Thu, 24 Jan 2002 11:09:34 +0800
Message-ID: <a2ntpf$dv41_at_imsp212.netvigator.com>
The group by function may be put inside the subquery:
SELECT DISTINCT
department.desc,
people.name,
acct_max_dollars,
expense_amount
FROM
department,
people,
(
SELECT people_id, sum(max_dollars) acct_max_dollars
FROM accounts
GROUP BY people_id
) TABLE_A,
(
SELECT accounts_id, sum(amount) expense_amount
FROM expenses
GROUP BY accounts_id
) TABLE_B
WHERE
department.id = people.department_id AND people.id = TABLE_A.people_id AND
account.id = TABLE_B.accounts_id
KC
"Michael" <cipriano_at_eng.utah.edu> wrote in message
news:2cf417fd.0201231648.2df0672e_at_posting.google.com...
> 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
> 4 3 2,000
> 5 3 3,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
> 10 5 2,000
>
> am I totaly lost, what do I need to do here?
>
>
> Thank you in advance
Received on Thu Jan 24 2002 - 04:09:34 CET