• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Syntax Issues: Sum if in SELECT statement


I need to update Code Block 1. to sum the values of Data.WeekValue if the value is greater than 25 and less than 50.

Code Block 1.
Sum(Data.WeekValue) AS WeekValues

Open in new window

I'm having troubles with the syntax, I've tried Code Block 2. but not sure how to:
1.  Assign a field name i.e. the AS WeekGreaterThan25
2.  If value isnt in the range I would like the value to be 0 only if no values are in the defined range (don't want to override the sum if the last value isn't in the range defined).

Code Block 2.
Sum(IIF(([Data].[WeekValue] > 25 AND [Data].[WeekValue] <50 ),  AS WeekGreaterThan25

Open in new window

  • 2
1 Solution
Try this:
Sum(IIF([Data].[WeekValue] > 25 AND [Data].[WeekValue] <50, [Data].[WeekValue], 0)  AS WeekGreaterThan25
You can also try:
Sum(IIF([Data].[WeekValue] Between 26 AND 49, [Data].[WeekValue], 0) AS WeekGreaterThan25
AndyC1000Author Commented:
When I run the first sum if example there's a syntax error missing operator in expression.
Sorry about that... both those expressions need an extra closing parenthesis.
Sum(IIF([Data].[WeekValue] > 25 AND [Data].[WeekValue] <50, [Data].[WeekValue], 0))  AS WeekGreaterThan25
Sum(IIF([Data].[WeekValue] Between 26 AND 49, [Data].[WeekValue], 0)) AS WeekGreaterThan25
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now