CODEWITHSUNDEEP
mysqlsqlcase
user2320239
user2320239

Reputation: 1038

Simplifying nested CASE expressions

I'm currenty working on the project that has an sql expressions to do some calculations in the database as follows:

SELECT
  SUM( 
    CASE models.type
    WHEN 'type_1'
      THEN ( CASE models.attribute_id 
             WHEN other_models.other_attribute_id
               THEN ROUND(models.value * other_models.conversion, 2)
             ELSE
               models.value   
             END ) * -1
    WHEN 'type_2'
      THEN ( CASE models.attribute_id 
             WHEN other_models.other_attribute_id
               THEN ROUND(models.value * other_models.conversion, 2)
             ELSE
               models.value   
             END )
    END
  ) as value

This works well enough, but it seems bad to me to have the outer case expressions THEN clauses to be pretty much exactly the same apart from the * -1. Is there a way to express this so that the first case expression just gets multiplied by the second something along the lines of:

  SELECT
    SUM( 
      CASE models.attribute_id 
      WHEN other_models.other_attribute_id
        THEN ROUND(models.value * other_models.conversion, 2)
      ELSE
        models.value   
      END * (CASE models.type
             WHEN 'type_1'
               THEN -1
             WHEN 'type_2'
               THEN 1
             END)
    ) as value

Seems like something that would be possible to do, but I'm not sure how.

Upvotes: 1

Views: 776

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You do not need to nest case expressions:

SELECT SUM(CASE WHEN models.type = 'type_1' AND models.attribute_id = other_models.other_attribute_id
                THEN - ROUND(models.value * 10)
                WHEN models.type = 'type_1'
                THEN - models.value   
                WHEN models.type = 'type_2' AND models.attribute_id = other_models.other_attribute_id
                THEN ROUND(models.value * 10)
                WHEN models.type = 'type_2'
                THEN models.value   
           END) as value

However, repeating all the logic seems unnecessary. If you only have the two types, I would go for:

SELECT (models.value *
        (CASE WHEN models.attribute_id = other_models.other_attribute_id THEN 10 ELSE 1 END) *
        (CASE WHEN models.type = 'type_1' THEN -1 ELSE 1 END)
       ) as value

These are only slightly more complicated if you have other types that you want to ignore.

I should also note that I don't recommending rounding before the aggregation, unless that is really what you intend. If you want the result to be rounded, then round afterwards.

Upvotes: 2

Related Questions