CODEWITHSUNDEEP
mysqlsqlmysql-workbench
user3287124
user3287124

Reputation: 67

How can I calculate the sum in a temporary column in SQL?

I have this code:

SELECT
    ID,
    name,
    result,
    CASE
        WHEN result = 1 THEN 100
        WHEN result = 2 THEN 80
        ELSE NULL
    END AS TemporaryColumn
FROM MyTable

And this is the table: 

| ID | Name | result | TemporaryColumn |
----------------------------------------
|  1 |  A   |   1    |      100        |
----------------------------------------
|  2 |  B   |   1    |      100        |
----------------------------------------
|  3 |  C   |   2    |      80         |
----------------------------------------
|  1 |  A   |   2    |      80         |
----------------------------------------
|  2 |  B   |   1    |      100        |
----------------------------------------
|  3 |  C   |   2    |      80         |
----------------------------------------

I want to find the sum() of every ID, and the table should look like this:

| ID | Name | result | TemporaryColumn |
----------------------------------------
|  1 |  A   |   1    |      180        |
----------------------------------------
|  2 |  B   |   1    |      200        |
----------------------------------------
|  3 |  C   |   2    |      160        |
----------------------------------------

How can I query this?

Upvotes: 0

Views: 2359

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166486

How about something like

SELECT
    ID,
    name,
    result,
    SUM(
        CASE
            WHEN result = 1 THEN 100
            WHEN result = 2 THEN 80
            ELSE NULL
        END
    ) AS TemporaryColumn
FROM MyTable
GROUP BY ID,
    name

Do not ethough that the result column cannot be ensured as from what I can tell, it is not unique, and should probably be excluded from the select list.

if that was the case, you would have to use

SELECT
    ID,
    name,
    SUM(
        CASE
            WHEN result = 1 THEN 100
            WHEN result = 2 THEN 80
            ELSE NULL
        END
    ) AS TemporaryColumn
FROM MyTable
GROUP BY ID,
    name

Upvotes: 1

Related Questions