asked on

Query help...

Let's say I have:

Select BankAccount, LastName from Accounts

and it lists all accounts and the account holders last name... one record per.

Now I want to see their total deposits... ever.

Deposits are in a AccountsTransactions table that has two fields

BankAccount and DepositAmount.

Select BankAccount, LastName, DepositAmount from Accounts, AccountsTransactions

will show a record for each deposit made...

Select BankAccount, LastName, Sum(DepositAmount) from Accounts, AccountsTransactions

will return one record with a grand total of all deposits made.

How do I accomplish this?

mbizup

Try this:

Select a.BankAccount, a.LastName, SUM(t.DepositAmount) as TotDeposits 
from Accounts a INNER JOIN AccountsTransactions t ON a.BankAccount = t.BankAccount
GROUP BY  a.BankAccount, a.LastName

Open in new window

Jim Horn

If you're getting at all deposits for each person, then ...

SELECT a.BankAccount, a.LastName, Sum(at.DepositAmount)
FROM Accounts a
JOIN AccountsTransactions at ON a.BankAccount = at.BankAccount
GROUP BY Select a.BankAccount, a.LastName

>will return one record with a grand total of all deposits made.
If you're getting at all deposits for all BankAccounts, then it would just be..

SELECT Sum(DepositAmount)
FROM AccountsTransactions

Sheldon Livingston

ASKER

So, if you "select" 15 fields and sum one field can I assume that you have to "group by" the other 14?

What if you select a, b, (c - d) + (e * f) as g, sum(h)

how do you "group by"?

Group by a, b, (c - d) + (e * f) or
Group by a, b, g

mbizup

You would group by the actual expressions:

Group by a, b, (c - d) + (e * f)

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.