SQL Query With Count, Sum, and JOIN

Question

I've sifted through posts to find an answer to my question, but I've had no luck. So I thought I would create a new one and see if the community might be able to help me out! Pardon me if I don't use the correct SQL terms in describing things.

Three tables in this problem. Table A is the "main" table with no dependencies. Table B has a foreign key reference to Table A. Table C has a foreign key reference to Table B.

There are two goals for this query. First is to sum up a field, let's say fieldC, in Table C. The only condition that must be met is a field in A, let's say fieldA, must be equal to, let's say X. To be fair, this solution is simple:

SELECT Sum(C.fieldC) FROM C
  INNER JOIN B
    ON C.foreign_keyB = B.id
  INNER JOIN A
    ON B.foreign_keyA = A.id
  WHERE fieldA = X

OK, so now the second goal: I would like to count the number of rows found in Table A that match fieldA = X. I tried this:

SELECT Sum(C.fieldC), Count(A.id) FROM C
  INNER JOIN B
    ON C.foreign_keyB = B.id
  INNER JOIN A
    ON B.foreign_keyA = A.id
  WHERE fieldA = X

But I'm getting totals that are way off. Is there some glaring mistake I'm making here? Thanks for the help everyone! =)

when I do a count as you are I tend to need groupBy also. If you just select A.id as your only column, do you get the same value repeated in many places? You may need to group by A.id, but then that will probably throw your sum off. — James Black
– James Black, Commented Dec 18, 2012 at 15:37
I'm sorry, I guess a third unmentioned goal is to achieve the first two goals in one query. I was just trying to be efficient. — Bobby Knezevic
– Bobby Knezevic, Commented Dec 18, 2012 at 15:41
Thanks, guys. Apparently just adding the keyword distinct was the answer in my case. — Bobby Knezevic
– Bobby Knezevic, Commented Dec 18, 2012 at 15:42

Gordon Linoff · Accepted Answer · 2012-12-18 15:37:25Z

1

In this case, you can fix the problem using count distinct:

SELECT Sum(C.fieldC), Count(distinct A.id)
FROM C
  INNER JOIN B
    ON C.foreign_keyB = B.id
  INNER JOIN A
    ON B.foreign_keyA = A.id
  WHERE fieldA = X

If you wanted to do other operations on fields in A, such as an average value or sum, then the solution would be a bit more complicated.

answered Dec 18, 2012 at 15:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Francis P Over a year ago

I don't think this is correct, there must be a simpler answer to this problem. DISTINCT is not really fixing the problem, it's fixing the results.

Bobby Knezevic Over a year ago

Well that was easy. Thank you, sir! =)

Collectives™ on Stack Overflow

SQL Query With Count, Sum, and JOIN

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related