CODEWITHSUNDEEP
sqlcase
ritratt
ritratt

Reputation: 1858

SQL AS query results in duplicate column

I have a SQL query like so

SELECT
    name,
    CASE WHEN (new_value=2) THEN 0 END as out,
    CASE WHEN (previous_value=2) THEN 1 END as out
    FROM my_table;

This results in duplicate columns:

name out  out
foo   1   null
bar   null  1

instead of 

name out
foo   1 
bar   0

How do I fix this?

Upvotes: 0

Views: 32

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13006

You are getting null output, so you need to add else on this.

select name,
    case 
        when new_value = 2 then 0 
        when previous_value = 2 then 1 
    else 0 end as out
from my_table;

Upvotes: 1

GMB
GMB

Reputation: 222482

Consider:

SELECT
    name,
    CASE 
        WHEN new_value = 2 THEN 0 
        WHEN previous_value = 2 THEN 1 
    END as out
FROM my_table;

In your query, each case expression generates one column in the resulset. You want only one, with two branches (denoted by when ... then ...)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You want one case expression with two conditions:

SELECT name,
       (CASE WHEN new_value = 2 THEN 0 
             WHEN previous_value = 2 THEN 1
        END) as out
FROM my_table;

Upvotes: 4

Related Questions