Questions tagged [case]

In SQL, the CASE expression evaluates a list of conditions, returning the result for the first condition which evaluates to true. For programming languages' CASE questions, use the [switch-statement] tag instead.

The CASE expression is an SQL construction used to return different values depending on specified conditions. There are two forms of the CASE expression. The first is the simple CASE:

CASE search_expr
WHEN compare_expr1 THEN result_expr1
WHEN compare_expr2 THEN result_expr2
ELSE else_expr
END

This will compare search_expr to each compare_expr in order until it finds a condition where search_expr = compar_expr, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

The second form of CASE expression is the searched CASE:

CASE 
WHEN boolean_expr1 THEN result_expr1
WHEN boolean_expr2 THEN result_expr2
ELSE else_expr
END

This will test boolean_expr in order until it finds a condition where the boolean_expr is true, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

There are two short forms for special CASE expressions; COALESCE and NULLIF.

COALESCE(x1, x2, ..., xn) 

is equivalent to:

CASE WHEN x1 IS NOT NULL THEN x1 
     WHEN x2 IS NOT NULL THEN x2
     ...
     ELSE xn
END 

I.e. the COALESCE expression returns the value of the first non-null operand, found by working from left to right, or null if all the operands equal null.

NULLIF(x1, x2) 

is equivalent to

CASE WHEN x1 = x2 THEN NULL ELSE x1 END

I.e. if the operands are equal, NULLIF returns null, otherwise the value of the first operand.

8090 questions
1
vote
3 answers

How to do a for loop with case_when

I'm a beginner with R and I'm trying to do a for-loop to recode many variables: when "test" modality is missing, then have "test.v1" modality. It looked very easy to do, but I can't get it: VEC_1 <-…
Curbice
  • 23
  • 3
1
vote
2 answers

How to create another column in a data frame based on repeated observations in another column?

So basically I have a data frame that looks like this: BX BY 1 12 1 12 1 12 2 14 2 14 3 5 I want to create another colum ID, which will have the same number for the same values in BX and BY. So the table would look like this…
starski
  • 141
  • 6
1
vote
2 answers

MySQL query CASE WHEN THEN in UPDATE statement

I am trying to execute the following statement but it is resulting in error. There is not much to explain the situation here as the query itself is self explanatory. However, I will attach the error below: Query UPDATE swipes SET CASE WHEN…
Mr. Crypto
  • 39
  • 5
1
vote
1 answer

how to get count of country column and as per count value, get result from other table in MYSQL

I have below tables I want to combine the result from two tables. As you can I have country column which contains a string value (max two countries not more than two). If we can split the string, resultant array should contain two elements and we…
micronyks
  • 54,797
  • 15
  • 112
  • 146
1
vote
1 answer

Use case_when() in R with multiple conditional rules and multiple columns

I need to create a new column (insider_class) sorting data from a data.frame` based on specific rules using two columns as a reference. I have a column with several parameters (parameter) and another with values (value). The rule is: If value pH…
wesleysc352
  • 579
  • 1
  • 8
  • 21
1
vote
3 answers

How to get distinct count along with different conditions

My data looks like this, I've 4 distinct Result category: Normal, Mild, Moderate and Severe I want to get count of patients for each categories and in case of severe category, I want to further divide it into more categories based on its…
User1011
  • 143
  • 1
  • 10
1
vote
1 answer

Using case_when with dplyr mutate: why is sometimes the value per row used for calculation and sometimes the whole column?

I want to create a new variable when two variables match a condition. In that case I want to have the smaller of two other variables. In my real data these are dates and I want to add other conditions in the case_when function, but I keep it simple…
PStaus
  • 15
  • 4
1
vote
3 answers

T-SQL CASE Statement to determine what is compared

T-SQL 2018 coding/logic question. I have CASE in a SELECT statement. Below I have provided the "pseudo code" of what the requirement is, but I need assistance in how to write the last AND of the CASE statement. Is it possible to change which…
user149104
  • 13
  • 2
1
vote
3 answers

.change() with case

is it possible to add a case statement to that? The thing is that now it takes the value from the droplist and writes it to #shop_price. I want to add a statement that when it receives for example "door", instead of writing that, it would write…
1
vote
3 answers

how to run block of code inside python case statement

I'm new to python and I really like concept of using dictionaries instead of switch/case statements, but there is one problem I can't figure out Let's say we have a 'pythonic case' statement { 'a': somemethod, 'b': othermethod }['a']() This…
Jan Vorcak
  • 19,261
  • 14
  • 54
  • 90
1
vote
1 answer

Mysql confusion with priority of order by

I am beginner in sql and this is the mysql code I was testing. CREATE TABLE test ( id INT, nm VARCHAR(16), occ VARCHAR(16) ); INSERT INTO test (id, nm, occ) VALUES (1, "E", "X"), (2, "B", "X"), (3, "C", "Y"), (4, "D", "Z"), (5, "A",…
LHC2012
  • 187
  • 6
1
vote
1 answer

SQL CASE and Union

any idea what is wrong with this statement. I am just trying to get grades from three different tables and take an average of that. then assign a pass/fail based on the average value. CREATE OR REPLACE MODEL…
Sreeni P
  • 11
  • 1
1
vote
1 answer

How to subtract two rows from two different selects?

I'm trying to subtract the total number shares for each symbol from the sell orders and the buy orders so I can have that total of shares owned. buy = db.execute("SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation =…
1
vote
1 answer

A better way to get the last character

I have been working in an exercise, I have 2 values (one with 9 characters and the other with 8) and I have to show 3 columns. The original number The number without the last character Only the last character The Query works but I would like to…
1
vote
4 answers

Remove or Replace with blank partial string in MySQL Query

I'm trying to replace only certain parts of a string via an SQL query. The column holds data formatted as so: United States Postal Service 
(Express Mail®
---Approx. delivery time 07-Sep-2011): I'm trying to make it…
PHRoG
  • 89
  • 1
  • 11
1 2 3
99
100