CODEWITHSUNDEEP
phpmysqlsumleft-join
rrfive
rrfive

Reputation: 165

LEFT JOIN with SUM not working

hoping I could get a hand with a LEFT JOIN + SUM issue I'm having.

The background: I'm building a wee finance system and want to calculate the value of all invoices within a given month (blank months = null). I have two tables:

tsm_finance_calendar - Containing 'months'. tsm_finance_invoices - Contains details of each invoice.

My query:

<?php
$query = "SELECT tsm_finance_calendar.month,
                 SUM(tsm_finance_invoices.totalBilled)
          FROM tsm_finance_calendar
          LEFT JOIN tsm_finance_invoices
              ON tsm_finance_calendar.month = tsm_finance_invoices.month
          GROUP BY tsm_finance_calendar.month
          ORDER BY 'id'"; 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['month']. " - $". $row['SUM(totalBilled'];
echo "<br />";
}
?>

Output is on the right track (Month - $Blank) but lacks the result of the sum.

Any help gets a giant high-five :)

Thanks, RR

Upvotes: 1

Views: 923

Answers (3)

rineez
rineez

Reputation: 752

Did u miss a closing parenthesis in 'SUM(totalBilled' there?

echo $row['month']. " - $". $row['SUM(totalBilled'];

And I wonder why you need a JOIN there if both the month field of tsm_finance_invoices is having similar values as tsm_finance_calendar.month ?

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86416

Use the as keyword in query

$query = "SELECT tsm_finance_calendar.month, SUM(tsm_finance_invoices.totalBilled) as sum FROM tsm_finance_calendar LEFT JOIN tsm_finance_invoices ON tsm_finance_calendar.month = tsm_finance_invoices.month GROUP BY tsm_finance_calendar.month ORDER BY 'id'"; 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['month']. " - $". $row['sum'];
echo "<br />";
}

Upvotes: 4

tekknolagi
tekknolagi

Reputation: 11022

$row["month"]-$row["SUM(totalBilled)"]

and you forgot to close a paren                        ^

Upvotes: 0

Related Questions