• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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?
0
classnet
Asked:
classnet
  • 3
  • 2
  • 2
2 Solutions
 
mbizupCommented:
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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
 
classnetAuthor Commented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mbizupCommented:
You would group by the actual expressions:

Group by a, b, (c - d) + (e * f)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>So, if you "select" 15 fields and sum one field can I assume that you have to "group by" the other 14?
Correct.  Whenever you apply an aggregate such as SUM(), COUNT(), MIN(), MAX(), you have to GROUP BY all other columns.  

There is an exception for if it's a hard-coded column such as '1' as some_code, and calculations such as the cdef one you posted.
0
 
mbizupCommented:
If you have a lot of additional fields in your accounts table that you want to include, you might also be able to use a subquery like this:


SELECT a.*, q.TotDeposit
FROM Accounts 
INNER JOIN
(SELECT BankAccount, SUM(DepositAmount) as TotDeposit
FROM AccountsTransactions
GROUP BY BankAccount) q ON a.BankAccount = q.BankAccount

Open in new window

0
 
classnetAuthor Commented:
Thank you very much!  This will help others I'm sure.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now