Selecting sum of a column in table1 based on table2?

Question

I have two tables, employees and transactions, as follows:

 employees: 
 EmployeeID
 firstname
 lastname

 transactions: 
 TransactionID
 employeeID
 amount

How would I get the total 'amount' for all employees with the firstname 'John'? Thanks!

 Select sum(amount) 
 from transactions 
 where employeeID = (everyone with first name John)

Reading towards joins will give you more idea. go through it — Chetan_Vasudevan, Sep 21 '17 at 15:36
study the basic concepts of SQL including joins. This is one of the most key parts, if you need to ask about this with no idea what to do then you haven't spent enough time studying before trying to start doing things. — ADyson, Sep 21 '17 at 15:37
@ChetanVasudevan Hey Chetan. Joins don't alter the tables in any way though do they? — Programmer, Sep 21 '17 at 15:40
@novice. Nope I meant to study joins because it would help you to connect between tables if related and draw data out from it. — Chetan_Vasudevan, Sep 21 '17 at 15:42

score 3 · Answer 1 · answered Sep 21 '17 at 15:36

3

You just need to do a JOIN:

Select  Sum(T.Amount)
From    Transactions    T
Join    Employees       E   On  E.EmployeeId = T.EmployeeId
Where   E.FirstName = 'John'

answered Sep 21 '17 at 15:36

Siyual

score 3 · Answer 2 · answered Sep 21 '17 at 15:36

3

try:

Select sum(amount)
from transactions
where employeeID 
   in (Select employeeID from employees 
       where firstname ='firstname');

thx ;)

answered Sep 21 '17 at 15:36

nimour pristou

score 3 · Answer 3 · answered Sep 21 '17 at 15:37

You were almost there. Try:

SELECT SUM(amount) 
FROM transactions t
INNER JOIN employees e
    ON t.employeeID = e.employeeID
WHERE e.firstName = 'John'

Alternately, this works as well

SELECT SUM(amount) 
FROM transactions t
WHERE t.employeeId IN (
    SELECT employeeID
    FROM employees e
    WHERE e.firstName = 'John'
    )

N00b Pr0grammer · Accepted Answer · 2017-09-24T09:21:50.670

3

An INNER JOIN should do the trick. Take a look at the query provided below:

SELECT SUM(T.amount) FROM
Transactions T INNER JOIN Employees E
ON T.EmployeeID = E.EmployeeID
WHERE E.FirstName = 'John'

edited Sep 24 '17 at 09:21

answered Sep 21 '17 at 15:37

N00b Pr0grammer

Beltaine · Answer 5 · 2017-09-21T15:42:55.017

2

Here's my guess. You have to join the two tables together, then filter the data that you want.

SELECT     SUM(tr.amount)
FROM       Employees emp
INNER JOIN transactions tr ON emp.employeeId = tr.EmployeeId
WHERE      emp.FirstName = 'John'

edited Sep 21 '17 at 15:42

answered Sep 21 '17 at 15:36

Beltaine

5 Answers5