CODEWITHSUNDEEP
phpmysql
SMacFadyen
SMacFadyen

Reputation: 3165

MySQL SUM(costs) WHERE id = X

I'm trying to select the SUM of a row where the ID matches a php integer.

It's turning the SUM of all my costs, not costs specific to the ID. 

$qry = mysql_query("SELECT SUM(myTable.myCostAmount) 
                    AS total, myOthertable.myOthertableID 
                    FROM myOthertable, myTable WHERE myOthertable.myOthertableID = '$myInt'");

So I am trying to select the ID from myOtherTable, and return the SUM of myCostamount from myTable. Do I need to use some sort of JOIN here, as the SELECT doesnt seem to be working with the WHERE.

I've thought about using a foreach to avoid this confusion, I just want to SUM the outputted results where the ID = X. (Stored in $myInt).

Upvotes: 1

Views: 211

Answers (3)

SMacFadyen
SMacFadyen

Reputation: 3165

I've used PHP for this. 

$grandTotal += $total;

Upvotes: -1

Eugen Rieck
Eugen Rieck

Reputation: 65314

SELECT
  SUM(myTable.myCostAmount) AS total,
  myOthertable.myOthertableID 
FROM 
  myOthertable
  INNER JOIN myTable ON myOtherTable.CommonField=myTable.CommonField
WHERE
  myOthertable.myOthertableID = '$myInt'

Upvotes: 1

nikc.org
nikc.org

Reputation: 16993

You are already using a join, as FROM myOthertable, myTable in your SQL is an implicit join.

What you need, though, is an explicit join, stating how the tables should be joined. Further, you need a GROUP BY, because otherwise an aggregate function makes no sense.

Without knowing your table structure or relation, I cannot give an exact answer, but it would be along the lines of:

SELECT SUM(myTable.myCostAmount) AS total, myOthertable.myOthertableID 
FROM myOthertable
LEFT JOIN myTable ON myTable.myOtherTableID = myOthertable.myOtherTableID
WHERE myOthertable.myOthertableID = '$myInt'
GROUP BY myTable.myOtherTableID

If you include your table structure (at least for the relevant columns), along with information how they are related, I can edit my response to a more exact solution.

Upvotes: 1

Related Questions