0

I am trying to calculate and show the results when the value is not equal to 0.00. When I execute the statement I can see all the values that are higher than 0.00, but the values that are lower than 0.00 are not shown.

I am executing the following statement:

SELECT sum(debit-credit-balance) as 'Open value'
FROM table
WHERE 'Open value' <> CAST('0.00' AS DECIMAL(50,2))

I can see all the values that are higher than 0.00. But I dont see the values that are lower than 0.00 (example: -100.00).

Does someone know what is wrong with this statement?

Update 1:

When I use backticks instead of quotes I get the following error message:

#1054 - Unknown column 'Open value' in where clause
John
  • 904
  • 8
  • 22
  • 56
  • it's working [Demo](https://paiza.io/projects/1Z7OkY7b4IOnIa6Oewgppg?language=mysql) which version of sql you are using? – Divyesh patel Oct 05 '19 at 04:19
  • The giveaway should have been not getting an `Unknown column in WHERE clause` error since you can't use aliases in WHERE clauses. Your problem is that in the `WHERE` clause, `'Open value'` is treated as the literal string `Open value`, not as a reference to the column alias you created using single quotes (which it never ceases to annoy me that MySQL allows when it should require backticks). Anyway, change it to `\`Open value\`` and move it to a `HAVING` clause and it should work e.g. https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2a9da01b22ed0afe07b84134832ea357 – Nick Oct 05 '19 at 05:21
  • I am working with MySQL version 5.5 When I execute the statement I get the following error message: `#1054 - Unknown column 'Open value' in where clause`. I replaced the single quotes with backticks in SELECT and WHERE – John Oct 05 '19 at 08:39
  • You can't use field alias in WHERE, it is only available in HAVING. Also you probably missed GROUP BY clause in your statement, because without GROUP BY, SUM returns an overall sum for entire table. – Naktibalda Oct 07 '19 at 12:28

0 Answers0