samedi 27 décembre 2014

Summing columns for each value in a different table

Given tables A and B as shown below:



Table A Table B
+----+---+ +----+-----+-------+
+ id + c + + id + Aid + value +
+----+---+ +----+-----+-------+
+ 1 + 1 + + 1 + 1 + 50 +
+----+---+ +----+-----+-------+
+ 2 + 0 + + 2 + 1 + 20 +
+----+---+ +----+-----+-------+
+ 3 + 1 + + 3 + 2 + 75 +
+----+---+ +----+-----+-------+
+ 4 + 3 + 23 +
+----+-----+-------+
+ 5 + 3 + 17 +
+----+-----+-------+


How does one get the sum of the column B.value for each value of A.id that meets the condition c=1?


That is, the following:



+----+-------+
+ id + value +
+----+-------+
+ 1 + 70 +
+----+-------+
+ 3 + 40 +
+----+-------+


I've tried doing SELECT A.id as id, SUM(B.value) as value FROM A INNER JOIN B on B.Aid=A.id WHERE c=1, but that just returns the following:



+----+-------+
+ id + value +
+----+-------+
+ 3 + 110 +
+----+-------+

Aucun commentaire:

Enregistrer un commentaire