SQL: How to check a value in column is equal to the sum of any combination of the other columns in a row

Syed Zaidi

I came across a scenario where I have a table structure as such:

Column0 Column1 Column2 Column3 Column4 Column5 
100     U       V       X       Y       Z

I need to find if:

100 = U, V, X, Y, or Z.
100 = X + Y + Z + V
100 = X + Y
100 = V + Z
etc.

Any suggestions of how I can achieve this?

Case statements will take forever to write. I do not write PL/SQL code but I am familiar with it somewhat.

Siyual

Writing the Case statement isn't as hard as you're implying. I generated the following Case statement using a text editor in about 20 seconds, which should handle all situations in your example:

Select  Case
            When Column0 = Column1  Then 1
            When Column0 = Column2  Then 1
            When Column0 = Column3  Then 1
            When Column0 = Column4  Then 1
            When Column0 = Column5  Then 1

            When Column0 = Column1 + Column2    Then 1
            When Column0 = Column1 + Column3    Then 1
            When Column0 = Column1 + Column4    Then 1
            When Column0 = Column1 + Column5    Then 1

            When Column0 = Column2 + Column3    Then 1
            When Column0 = Column2 + Column4    Then 1
            When Column0 = Column2 + Column5    Then 1

            When Column0 = Column3 + Column4    Then 1
            When Column0 = Column3 + Column5    Then 1

            When Column0 = Column4 + Column5    Then 1

            When Column0 = Column1 + Column2 + Column3  Then 1
            When Column0 = Column1 + Column2 + Column4  Then 1
            When Column0 = Column1 + Column2 + Column5  Then 1

            When Column0 = Column1 + Column3 + Column4  Then 1
            When Column0 = Column1 + Column3 + Column5  Then 1

            When Column0 = Column1 + Column4 + Column5  Then 1

            When Column0 = Column2 + Column3 + Column4  Then 1
            When Column0 = Column2 + Column3 + Column5  Then 1

            When Column0 = Column2 + Column4 + Column5  Then 1

            When Column0 = Column3 + Column4 + Column5  Then 1

            When Column0 = Column1 + Column2 + Column3 + Column4    Then 1
            When Column0 = Column1 + Column2 + Column3 + Column5    Then 1

            When Column0 = Column1 + Column3 + Column4 + Column5    Then 1

            When Column0 = Column2 + Column3 + Column4 + Column5    Then 1

            When Column0 = Column1 + Column2 + Column3 + Column4 + Column5  Then 1

            Else 0
        End As SumOfOtherColumns
From    YourTable

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-11-17

Comments

0 comments

Sum column if other columns equal value

Check if any combination of columns are equal some specific value in matrix

How to change a value in a column based on whether or not a certain string combination is in other columns in the same row? (Pandas)

How to check one rows value present in any of the other column row value

SQL: How to check a value in column is equal to the sum of any combination of the other columns in a row

SQL: How to check a value in column is equal to the sum of any combination of the other columns in a row

pump.io port in URL

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

How to import an asset in swift using Bundle.main.path() in a react-native native module

Inner Loop design for webscrapping

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Removed zsh, but forgot to change shell back to bash, and now Ubuntu crashes (wsl)

Ambiguous use of 'init' with CFStringTransform and Swift 3

Resetting Value of <input type="time"> in Firefox

Execute ./script.sh with a crontab

Converting a class method to a property with a backing field

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

How to update azerothcore-wotlk docker container

How to set tab order for array of cluster,where cluster elements have different data types in LabVIEW?

Grails with Oracle thick OCI driver authenticate to Oracle with wrong user

How to pass data to the ng2-bs3-modal?

Making Array From Page Elements in jQuery

Retrieve Element Tag Value XML Using Bash

Laravel's ORM sync with timestamps doesn't update timestamps

Do animations stop css changes after animation completion?