SQL sum with condition

by

Try this instead:

SUM(CASE WHEN ValueDate > @startMonthDate THEN cash ELSE 0 END)

Explanation

Your CASE expression has incorrect syntax. It seems you are confusing the simple CASE expression syntax with the searched CASE expression syntax. See the documentation for CASE:

The CASE expression has two formats:

  • The simple CASE expression compares an expression to a set of simple expressions to determine the result.
  • The searched CASE expression evaluates a set of Boolean expressions to determine the result.

You want the searched CASE expression syntax:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

As a side note, if performance is an issue you may find that this expression runs more quickly if you rewrite using a JOIN and GROUP BY instead of using a dependent subquery.

More Related Contents:

  1. Get Parent with multiple children in desc order
  2. How can I schedule a job to run a SQL query daily?
  3. Convert Month Number to Month Name Function in SQL
  4. How do I do top 1 in Oracle? [duplicate]
  5. Multiple INSERT statements vs. single INSERT with multiple VALUES
  6. Removing leading zeroes from a field in a SQL statement
  7. Database Structure for Tree Data Structure [closed]
  8. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  9. sql ORDER BY multiple values in specific order?
  10. MySQL’s now() +1 day
  11. Oracle SQL – Identify sequential value ranges
  12. ColdFusion adding extra quotes when constructing database queries in strings
  13. NOT NULL constraint over a set of columns
  14. In SQL, is UPDATE always faster than DELETE+INSERT?
  15. How can I determine the status of a job?
  16. MySQL #1140 – Mixing of GROUP columns
  17. How can I select rows with most recent timestamp for each key value?
  18. How to delete the top 1000 rows from a table using Sql Server 2008?
  19. SQL Error: ORA-00942 table or view does not exist
  20. CHECK CONSTRAINT on multiple columns
  21. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  22. What are the use cases for selecting CHAR over VARCHAR in SQL?
  23. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  24. How to extract week number in sql
  25. Why do multiple-table joins produce duplicate rows?
  26. How do I format date and time on ssrs report?
  27. In Oracle, is it possible to INSERT or UPDATE a record through a view?
  28. Insert data and set foreign keys with Postgres
  29. How to use a ring data structure in window functions
  30. How to check db2 version

Leave a Comment