Limit SQL Sum function by value in another table?

Question

I'm trying to get the sum of customers' balances from a specific sales rep. Here's my code:

SELECT SUM(CusBalance)
From Customer, Rep
WHERE
Rep.RepFName = 'Tim'
AND
Rep.RepLName = 'Sanchez';

However, I'm just getting the sum of every customer's balances.

I must be making a simple mistake, but I'm brand new to SQL and a little stuck. Any help would be appreciated. BTW, I'm doing this in Access 2013.

the [join](http://en.wikipedia.org/wiki/Join_%28SQL%29) condtion is missing. Usually a field in `Rep` should be matched with a field in `Customer`, but I can't tell without the schemas — Diego, Apr 05 '15 at 00:48

jpw · Answer 1 · 2015-04-05T01:02:48.620

2

You need to join (connect) the two tables via a shared column (usually a primary/foreign key relation).

Using the modern explicit ANSI join syntax (instead of the implicit joins in the where clause) the query would look like:

SELECT SUM(C.CusBalance) AS "Balance"
FROM Customer C 
INNER JOIN Rep R ON R.RepNum = C.RepNum
WHERE R.RepFName = 'Tim'
  AND R.RepLName = 'Sanchez';

edited Apr 05 '15 at 01:02

answered Apr 05 '15 at 00:57

jpw

score 0 · Answer 2 · answered Apr 05 '15 at 00:45

0

SELECT SUM(CusBalance) From Customer, Rep WHERE Rep.RepFName = 'Tim' AND Rep.RepLName = 'Sanchez' group by Rep.id

answered Apr 05 '15 at 00:45

Joshua Byer

score 0 · Answer 3 · answered Apr 05 '15 at 00:52

0

I think you are joining two tables if I am not mistaken, try look at joins, though syntax might vary on the type of SQL you are using but try:

SELECT C.SUM(CusBalance)
From Customer as C, Rep as R
WHERE
C.primarykey=R.foreignkey
AND
R.RepFName = 'Tim'
AND
R.RepLName = 'Sanchez';

answered Apr 05 '15 at 00:52

hass.

Yes, this was the issue, thank you all for the guidance! – CrashRattigan Apr 05 '15 at 01:01

score 0 · Answer 4 · answered Apr 05 '15 at 01:00

0

In case someone else stumbles upon this, it was answered by the community. I had to add the join condition:

WHERE Customer.RepNum = Rep.RepNum

answered Apr 05 '15 at 01:00

CrashRattigan

You do not need to add your own answer - a comment on the accepted answer is fine. Plus, you should be using `JOIN` instead of a `WHERE` clause to match the two tables. – D Stanley Apr 05 '15 at 01:04
@D Stanley in query processing both are equivalent to each other. The `INNER JOIN` is considered the explicit join (the newer ANSI standard) and `WHERE` is considered the implicit join. Both follow same execution plan. See [SO discussion](http://stackoverflow.com/questions/121631/inner-join-vs-where) – Parfait Apr 05 '15 at 01:31

4 Answers4