## Enterprise Guide Computed Column: Conditional Sum aka SumIF

Solved
Occasional Contributor
Posts: 13

# Enterprise Guide Computed Column: Conditional Sum aka SumIF

I would like to sum a column, "Sales", with the following conditions:

1. Sales amounts does not equal amount in "Returns" column.
2. Sales amounts does not equal amounts in "Rebates" column.

Can I do this with a Computed Column in Enterprise Guide?  If not, please help with code.  Thank you.

Accepted Solutions
Solution
‎02-07-2018 11:53 AM
Occasional Contributor
Posts: 13

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

[ Edited ]

Thank you all for the great help.

I ended up using something similar to FredrikE, but with an "OR".  I also added another WHEN statement I needed.

```CASE
WHEN ((Return_Amt + Store_Credit) = Sale_Amt)
THEN Return_Amt
WHEN Sale_Amt ne Return_Amt AND Sale_Amt ne Rebate_Amt
THEN Sale_Amt
ELSE 0 END```

Then I sum the column in the next query.

For some reason when I put the first "WHEN" statement with the formula below the other, the Sale_Amt would populate, not the Return_Amt.  I suppose the first "THEN" takes precedence on the CASE statement.  Thank you, everyone.

All Replies
Super User
Posts: 23,951

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Fistful_Dollars wrote:

Can I do this with a Computed Column in Enterprise Guide?  If not, please help with code.  Thank you.

Yes you can use computed columns for this.

Create two columns using CASE statements, one for condition #1 and one for condition #2.

Then sum each of those new columns.

PROC Star
Posts: 548

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

just expanding on what @Reeza suggested

sum( case when sales_amount ne Returns then sales_amount else 0 end) as computed_column1,

sum( case when sales_amount ne Rebates then sales_amount else 0 end) as computed_column2

Super User
Posts: 23,951

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

@kiranv_One of those should be eq not ne

PROC Star
Posts: 391

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Why use two columns?

Why not just add this condition in the computed colums editor and then select aggregation sum?

case

when (Sales_amounts ne Returns OR Sales_amounts ne Rebates) then sales_amount)

else 0

end

Depending on the logic use AND instead of OR...

//Fredrik

Super User
Posts: 23,951

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

@FredrikE is correct, I was reading that as creating two different sums. If it's a single sum you can definitely use the OR condition within the CASE statement.

Solution
‎02-07-2018 11:53 AM
Occasional Contributor
Posts: 13

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

[ Edited ]

Thank you all for the great help.

I ended up using something similar to FredrikE, but with an "OR".  I also added another WHEN statement I needed.

```CASE
WHEN ((Return_Amt + Store_Credit) = Sale_Amt)
THEN Return_Amt
WHEN Sale_Amt ne Return_Amt AND Sale_Amt ne Rebate_Amt
THEN Sale_Amt
ELSE 0 END```

Then I sum the column in the next query.

For some reason when I put the first "WHEN" statement with the formula below the other, the Sale_Amt would populate, not the Return_Amt.  I suppose the first "THEN" takes precedence on the CASE statement.  Thank you, everyone.

PROC Star
Posts: 391

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

As soon as a case/when evaluation is true it stops the evaluation, so more important "when"'s should come first

//Fredrik

PROC Star
Posts: 548

## Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

@Reeza you are right, looks like I did not understand the question properly

☑ This topic is solved.