CODEWITHSUNDEEP
phpmysql
Whit
Whit

Reputation: 1159

Adding WHERE YEAR breaks calculation

I have a very simple query:

SELECT a, b, a+b as c FROM records

Which works just great. He is an example from the results:

Array ( [a] => 100.92 [b] => 21.00 [c] => 121.92 )

But the minute I try to filter the records by year:

SELECT a, b, a+b as c FROM records
WHERE YEAR(`mydate`) = '2011'

The a+b calculation disappears:

Array ( [a] => 100.92 [b] => 21.00 [c] => )

Am I missing something obvious?

Updates:

  1. I've been asked for the actual SQL, here it is:

    SELECT credit, debit, credit+debit as total FROM transactions WHERE YEAR(transaction_date) = '2011'

  2. The transaction_date is a DATE field-type.

  3. Also, either the credit or debit field is always zero.

  4. PHP Code:

Code

include($_SERVER["DOCUMENT_ROOT"] . "/pool/_/db.php");

$sql = "SELECT credit, debit, credit+debit as total FROM transactions
            WHERE YEAR(`transaction_date`) = 2011";

$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
        print_r($row);
}

Upvotes: 4

Views: 111

Answers (1)

Sean Bright
Sean Bright

Reputation: 120704

The only thing I can think is that either credit or debit is NULL (not 0) which, in turn, would make the value of total NULL as well. Try this and see if it changes anything:

... COALESCE(credit, 0) + COALESCE(debit, 0) as total ...

Upvotes: 3

Related Questions