Sql Case Operator
The SQL CASE is used to provide if-then-else type of logic to SQL. It is used to control the execution of other sets of statements.
The SQL CASE statement goes through conditions and return a value or result when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading next condition and return the result or value. If no conditions are true, it returns the value or data in the ELSE clause.
If there is no ELSE statement (is optional) part and no conditions are true, then it returns NULL value.
The SQL CASE can be used in any valid sql statement or expression.
Related Links
Sql Case Syntax
The below syntax is used to create case statements.
CASE
WHEN condition_1 THEN value_1_or_result_1
[WHEN condition_2 THEN value_2_or_result_2]
[ELSE value_or_result]
END CASE
- You can provide any valid sql expression in the condition section.
Sample Database Table - Student
ID | SName | Gender | Dept | AvgMarks |
---|---|---|---|---|
1 | Varshini | F | CS | 78 |
2 | Rishi | M | IT | 65 |
3 | Kumar | M | CS | 34 |
4 | Vidya | F | CS | 55 |
5 | Devi | F | IT | 23 |
6 | Siva | M | IT | 88 |
SQL Case Example
The following SQL statement goes through conditions on "gender" column and returns a value when the first condition is met:
SELECT ID, SName,
CASE
WHEN Gender ='F' THEN 'Female'
WHEN Gender ='M' THEN 'Male'
END AS NewGender
FROM Student
In the above query, we have included the CASE statement with the "gender" column itself. You can check each row to see whether "gender" meets the condition "gender" = 'F' or "gender" = 'M' and then see the result in the column generated using the CASE statement.
The result of above query is:
ID | SName | NewGender |
---|---|---|
1 | Varshini | Female |
2 | Rishi | Male |
3 | Kumar | Male |
4 | Vidya | Female |
5 | Devi | Female |
6 | Siva | Male |
The following SQL statement goes through conditions on "AvgMarks" column and returns a value when the first condition is met:
SELECT ID, SName, Gender,
CASE
WHEN AvgMarks > 80 THEN 'A Grade'
WHEN AvgMarks > 60 AND AvgMarks <= 80 THEN 'B Grade'
WHEN AvgMarks > 40 AND AvgMarks <= 60 THEN 'C Grade'
ELSE 'No Grade'
END AS Grade
FROM Student
In the above query, we have included the CASE statement with ELSE section.
In the above example, if the value in the "avgmarks" column of a given row is "65", it will produce a result of "B Grade".
Here is what happens if the value in the "avgmarks" column is "58", SQL will do the following:
- Check to see if average is greater than 80. "58" is not greater than 80, so move on to the next WHEN/THEN
- Check to see if average is between 60 and 80. "58" is not between 60 and 80, so move on to the next WHEN/THEN
- Check to see if average is between 40 and 60. "58" is between 40 and 60, so record "C Grade" in the "Grade" column.
The result of above query is:
ID | SName | Gender | Grade |
---|---|---|---|
1 | Varshini | F | B Grade |
2 | Rishi | M | B Grade |
3 | Kumar | M | No Grade |
4 | Vidya | F | C Grade |
5 | Devi | F | No Grade |
6 | Siva | M | A Grade |
Related Links
SQL Case and Coalesce
According to the Microsoft document, the aim of SQL Server Coalesce and SQL Case statements is to verify expression value. A syntactic shortcut for the CASE expression is the Coalesce expression.
When the calls CASE and COALESCE are interchangeable. The following are the intended actions for this investigation: Replace one with a 'COALESCE' call and the other with a CASE expression.
Syntax:
Syntax of CASE Condition statement and Coalesce function
CASE
WHEN (expression1 is not NULL) THEN expresssion1
WHEN (expression2 is not NULL) THEN expression2
……
ELSE expression
END
SELECT COALESCE (expression1, expression2…) as ‘Result value’
Example:
SELECT
-- this CASE may be replaced by COALESCE
CASE
WHEN C1 IS NOT NULL THEN C1
ELSE 0
END
FROM dual;
The CASE statement in the instance can be replaced with SELECT COALESCE(C1, 0), which yields the same results.
Choose Prefer CASE expressions over COALESCE function on the inspection page if you choose using CASE expressions.
SQL Case Concat
Example 1: combine a CASE expression with a concatenation operator:
SELECT prodID, CASE prodSTATUS
WHEN 1
THEN 'SENT'
ELSE 'BACK ORDER'
END || ' STATUS'
FROM t1;
Example 2: There are various fields with 1 or 0 values. Each column name represents the name of one of our products. So, just as with "FirstName + '' + LastName," I'd like you to aggregate all of these fields into one field.
CASE
WHEN column1 = 1 THEN 'column1'
ELSE ''
END
+ ' '
+ CASE
WHEN column2 = 1 THEN 'column2'
ELSE ''
END as ConcatenatedColumn
SQL Case Count
SQL counts a column while also including a case statement, resulting in a count with case query.
Example 1: There are many ways to count and summarize with a case statement:
select
first_name|| ' ' || last_name "NAME",
case when upper(attk.status) like 'IN PROCESS%'
then count(attk.status) else 0 end "IN PROCESS",
case when upper(attk.status) like 'PENDING%'
then count(attk.status) else 0 end "PENDING",
case when upper(attk.status) like 'CLOSE%'
then count(attk.status) else 0 end "CLOSE"
from mytab;
Example 2: Query for CASE WHEN to set condition and count :
select count( case when Score=45 then 1 else NULL end) as SpecificCondition from DemoTable1374;
Output :
SpecificCondition |
---|
3 |
Example 3: count distinct records( eg:count distinct column1||column2) using the case statement.i could able to count total records in a group using following sample query but its including the duplicate records.
select dptno,count (case when condition1 then 1 end )cnt1,
count (case when condition2 then 1 end )cnt2,
count (case when condition3 then 1 end )cnt3
from emp
group by dptno
SQL Case With Different Datatypes
In this blog post titled "Different data data types in case expression," we'll look at how CASE expression behaves when dealing with various data types in the THEN (true) and ELSE (false) parts. This post's conversation will be limited to data conversion issues alone.
Example 1: A case statement can only return one data type. So convert the numbers to strings:
SELECT CASE
WHEN fourthlevel.case_type IN ('Complaint')
THEN
(SELECT cast(COUNT(*) as varchar2(255))
FROM work_days1
WHERE work_days1.business_date > fourthlevel.cdate
AND work_days1.business_date <=
COALESCE (fourthlevel.close_date, SYSDATE))
WHEN fourthlevel.case_type IN ('Enquiry')
THEN
(SELECT cast(COUNT(*) as varchar2(255))
FROM work_days1
WHERE work_days1.business_date > fourthlevel.create_date
AND work_days1.business_date <=
COALESCE (fourthlevel.close_date, SYSDATE))
WHEN fourthlevel.case_status = 'Cancelled'
THEN
'N/A'
END AS sla_days
FROM fourthlevel
When the two requirements do not meet, you could also return NULL.
Example 2: Handling different data types in CASE:
To deal with such situations, we can type cast both portions of the CASE expressions (THEN and ELSE) into the same data type, as seen below.
After type cast – In case true part returns
DECLARE @RetValThen DATETIME = '20150310'
DECLARE @RetValElse INT = 1
SELECT CASE WHEN 1 = 1 THEN CAST(@RetValTHEN AS VARCHAR(50)) ELSE CAST(@RetValELSE AS VARCHAR(50)) END
SQL Case Distinct
Example 1: We have this sales table recording the name of item sold and name of the customer who bought it.
item customer_name
book Alex
pen Bob
book Alex
book Jim
shoes Jim
pen Bob
backpack Bob
book Bob
wallet Alex
wallet Alex
book Bob
backpack Jim
A customer might purchase an item multiple times, so to count the number of unique item purchased by each customer:
SELECT
COUNT(DISTINCT CASE WHEN customer_name = 'Alex' THEN item END) AS by_Alex,
COUNT(DISTINCT CASE WHEN customer_name = 'Bob' THEN item END) AS by_Bob,
COUNT(DISTINCT CASE WHEN customer_name = 'Jim' THEN item END) AS by_Jim
FROM
sales;
Output:
by_Alex | by_Bob | by_Jim |
---|---|---|
2 | 3 | 3 |
Example 2: Include DISTINCT in your CASE WHEN statements with COUNT. Take a look:
SELECT
COUNT(DISTINCT CASE
WHEN pay_date BETWEEN '2015-06-01' AND '2015-06-05'
THEN candidate_id
END) AS accepted_student,
COUNT(DISTINCT CASE
WHEN pay_date = '2015-06-06'
THEN candidate_id
END) AS conditionally_accepted_student,
COUNT(DISTINCT CASE
WHEN pay_date > '2015-06-06'
THEN candidate_id
END) AS not_accepted
FROM application;
I counted the number of approved, conditionally acceptable, and rejected payments. Now we're going to count how many candidates were accepted for at least one degree course, how many were conditionally approved for at least one degree course, and how many were not permitted for at least one degree course.
We should have included the keyword DISTINCT since a single candidate can apply for several degree courses, thus if a candidate paid on time for two courses, that candidate would be included twice (the candidate could still be counted twice in two distinct columns if the candidate paid for one degree course but did not pay for another, but that's another matter).
Example 3: Trying to do a case on a distinct value and it isn't going well:
SELECT
CASE WHEN DISTINCT(Response_Master_Incident.Battalion) = '' THEN 'Unknown'
ELSE DISTINCT(Response_Master_Incident.Battalion) END AS 'Zone'
SQL Case Exists
Example 1: Using EXISTS clause in the CASE statement to check the existence of a record:
DECLARE @CustId INT = 2
SELECT (CASE WHEN EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
WHERE CustId = @CustId) THEN 'Record Exists'
ELSE 'Record doesn''t Exists' END) AS [Employee?]
Example 2: Oracle's Case-When-Exists expression is really useful. Here's an instance of how to return a status using it in a sub-select. To establish the person status, this SQL looks for a match between the PS PERSON and PSOPRDEFN records. The concept is that if an operator isn't in PS PERSON, they aren't a legitimate PeopleSoft person.
select O.OPRID,
O.EMPLID,
case when exists (
select 1
from PS_PERSON P
where P.EMPLID = O.EMPLID
) then 'Person' else 'Not a Person' end as PERSON_STATUS
from PSOPRDEFN O;
Example 3: CASE EXISTS :
SELECT * FROM dbo.CompanyMaster
WHERE AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
FROM dbo.AreaSubscription
WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
THEN @AreaId ELSE AreaId END)
AND AreaId IN (SELECT [@Areas].AreaId FROM @Areas)
SQL Case Having
To limit the rows returned by the SELECT operation, utilize the CASE expression in a HAVING clause.
Example 1: The HumanResources statement returns the maximum hourly wage for each job title. Table for employees. The HAVING clause limits the titles to those held by males earning more than $40 per hour or women earning more than $42 per hour.
USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = ‘M’
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = ‘F’
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;
Example 2: HAVING clause with CASE
USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1
ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = 'F'
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;
SQL Case If
From top to bottom, a CASE statement and a sequence of IF/ELSE IF statements are assessed. Only one branch will be performed at most: the first one with a true condition.
Example 1: CASE inside IF ELSE. Below is the example MS-SQL code
DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 190;
IF @Flight_Ticket > 400
PRINT 'Visit Nearby Tourist Location';
ELSE
BEGIN
SELECT
CASE
WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
END AS Location
END
In the above example CASE is NESTED inside IF…ELSE statement:
If the Case Condition in SQL Server is False, the IF statement will be executed first, followed by the ELSE statement.
Example 2: Duplicating a condition will always result in dead code. A copy/paste error is usually to blame. At most, it's just dead code; at worst, it's a flaw that will almost certainly cause other defects as the code is managed, and it might obviously cause unexpected behaviour.
Compliant Solution
IF @x = 1
PRINT 'A'
ELSE IF @x = 2
PRINT 'B'
ELSE IF @x = 3
PRINT 'C'
SELECT
CASE col1
WHEN 1
THEN 'A'
WHEN 2
THEN 'B'
WHEN 3
THEN 'C'
ELSE 'D'
END
FROM table1
SQL Case InnerJoin
Example: You appear to be attempting to create Where-clauses in the case, but you should alternatively compare the case's outcome to Call_Type_ ID (or any other field you like). I also use brackets above my casing to make it easier to understand where they begin and end.
INNER JOIN datamartend.dbo.Call_Type_Dim ON
(CASE
WHEN CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned IS NULL
AND AnsTime > 0
AND CallState IS NULL
THEN 10
WHEN CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned IS NULL
AND AnsTime > 0
AND CallState = 1
THEN 11
WHEN
CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned = 1
AND AnsTime IS NULL
AND CallState IS NULL
THEN 12
ELSE 1
END) = Call_Type_ID -- Insert something here to join on.
SQL Case Isnot Null
In SQL, the IS NOT NULL condition is used to check for a value that is not NULL. If a non-NULL value is detected, TRUE is returned; otherwise, FALSE is returned. In a SELECT, INSERT, UPDATE, or DELETE statement, it can be used.
This occurs because the values IS NULL and IS NOT NULL cannot be compared. So, the proper syntax is CASE WHEN, followed by the conditional expression including IS NULL or IS NOT NULL.
Syntax:
CASE and IS NOT NULL syntax.
CASE
WHEN (ID IS NULL)
THEN 'YES'
WHEN (ID IS NOT NULL)
THEN 'NO'
END AS ID_Value,
Example: We must say that this technique wouldn’t work in all cases, though. For instance, consider the following query:
SELECT e.emp_no,
e.first_name,
e.last_name,
CASE
WHEN dm.emp_no IS NOT NULL THEN 'Manager'
ELSE 'Employee'
END AS is_manager
FROM
employees e
LEFT JOIN
dept_manager dm ON dm.emp_no = e.emp_no
WHERE
e.emp_no > 109990;
SQL Case Isnull
If no TRUE value/condition is detected, the CASE statement returns the value specified in the ELSE clause. The CASE statement will return NULL if the ELSE clause is missing and no condition is discovered to be true.
The result sets are based on a data comparison with some values, and any values in the result set that are null must be replaced with an empty string acquired using the ISNULL function.
Example 1: If a column has blank values, -, and NULL values, and only regular text values in 0, then it is considered a blank column. I can only do the first two or the NULL values, not both. I tried putting ISNULL ([Family Level 2],0) in a separate case statement, as well as the existing case statement and a when statement.
CASE
WHEN column IS NULL THEN 'value'
WHEN column = 'value2' THEN 'valuexx'
ELSE 'othervalue'
END;
Example 2: We may use the CASE structure to accomplish this, however the CASE fieldname WHEN technique does not allow us to call a function, therefore we must use the CASE WHEN fieldname condition instead:
SELECT Name,
CASE WHEN color = 'black' THEN 'b'
WHEN color = 'red' THEN 'r'
WHEN color IS NULL THEN 'empty'
else 'n/a'
END AS Color_code
FROM SalesLT.Product;
Example 3: This should be included in a SELECT query with a CASE expression. I coded something similar, except the column's result set returns 0 instead of an empty string. This column ABC's data type is varchar(10):
SELECT top 20
ABC
,CASE
WHEN ABC IN (8,9,10,13,14,15,16,17,19,20,21) THEN 10
WHEN ABC IN (31,32,33) THEN 30
WHEN ABC IN (53,52,54,55) THEN 50
WHEN ABC IN (61,62) THEN 60
WHEN (ABC IS NULL AND DEF IS NOT NULL) THEN 99
else ISNULL(ABC,'')
END
FROM XYZ
SQL Case Orderby
After ORDER BY, use CASE to check for column values. You can also utilise ORDER BY with CASE statements that are simple and searched.
The ORDER BY clause can include the CASE statement, which then tests for column values. The ORDER BY clause in SQL is used to sort the results either ascending or descending.
Example 1: The following example demonstrates how to sort data using CASE. Consider retrieving data from the furniture data database, but only the rows with an even number of points should be shown at the top of the results. This cannot be accomplished using columns as sorting criteria; instead, CASE is required.
SELECT id, furniture, points
FROM furniture _data
ORDER BY CASE
WHEN points%2=0 THEN 1
ELSE 2
END ;
Output:
id furniture points
2 sofa 8
3 table 2
1 chair 5
4 bookcase 5
5 bed 3
It determined if the number in the points column could be divided by two without leaving a remainder. If it could, it returned a 1 and the row was displayed at the top of the results.
Example 2: we use the CASE after ORDER BY and then checks for column value:
SELECT * FROM PersonalDetails
ORDER BY
CASE Active WHEN 1 THEN Active END ASC,
CASE WHEN Active = 0 THEN LastName
ELSE FirstName END DESC
In above case, all records having Active = 1 is sorted on “Active ASC” order. All records having Active = 0 is sorted on ‘LastName DESC’ else ‘FirstName DESC’ order.
Example 3: SELECT with ORDER BY CASE
The ORDER BY and CASE statements are used in conjunction in the following SQL query.
SELECT * FROM Country
ORDER BY CASE WHEN cname='other' THEN 1 ELSE 0 END
Output:
ID cname
1 Afghanistan
2 Australia
3 France
4 Oman
6 Singapore
7 United States
5 Other
The ORDER BY clause combined with CASE moves the "Other" option to the bottom. This reasoning can be used for other things besides country, such as university name, city, state, and so on.
Example 4: Continuing with the sales manager's request, imagine she also wants to see the products sorted by price range, then by product name. We've seen how to make the price ranges appear as a column, but how do we arrange them?
Because CASE is an expression, it can be used as one of the values used to sort the results. Remember that we can sort an expression as well as table columns.
Here is the query to sort by the price range.
SELECT Name,
ListPrice
FROM Production.Product
ORDER BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END,
Name
SQL Case Subqueries
Use a subquery with a 'where' condition inside a Case statement to bind to the parent query.
Example 1: use SUBQUERY in the Case Statement :
CASE
WHEN (SELECT COUNT(MEDAL) FROM Table
= 10 THEN 'GOOD '
WHEN (SELECT COUNT(MEDAL) FROM Table
= 5 THEN 'POOR'
END
AS Rank
Example 2:
select
case when salary between 6 and 8 then '6-8'
when salary in (9,10) then '9-10'
when exists (select null from avg_sal where avg_sal = salary)
then 'EXISTS'
when to_char(salary) like '2%' then 'Like2'
when salary is null then 'Null'
else 'ELSE Empno: '|| emp_no
end
AS case_test
from emp
Output:
CASE_TEST |
---|
Null |
Example 3:
Select T.idperson , CASE WHEN T.type = 'C' THEN (SELECT name from Customers where C.idcustomer = T.idperson)
ELSE
(SELECT name from Providers where idprovider = T.idperson)
END Name
from myTable T
SQL Case Sum
You know that CASE WHEN can yield numbers because of the method SUM() and the constructs CASE WHEN.
Example 1: Use SUM and CASE in the SQL statement.
To fix this, use MySQL's SUM and CASE functions on the statement. As a result, the statement should be:
SELECT c.id, c.name,
SUM(CASE WHEN p.status=1 THEN 1 ELSE 0 END) as product_count
FROM product_categories c left join products p on c.id=p.category_id
GROUP BY c.id, c.name
Output:
id name product_count
1 category A 1
2 category B 2
3 category C 0
4 category D 0
Example 2: There are many ways to count and summarize with a case statement, To work with sum Aggregared function:
select
first_name|| ' ' || last_name "NAME",
sum(case when upper(attk.status)
like 'IN PROCESS%'
then 1 else 0 end) "IN PROCESS",
sum(case when upper(attk.status)
like 'PENDING%'
then 1 else 0 end) "PENDING",
sum(case when upper(attk.status)
like 'CLOSE%'
then 1 else 0 end) "CLOSE"
from mytab;
Example 3: Let's combine all of this data to create a query that can count many groups of rows at once. Take a look:
SELECT
SUM(CASE
WHEN scholarship IS TRUE THEN place_limit
ELSE 0
END) AS scholarship_places,
SUM(CASE
WHEN scholarship IS FALSE THEN place_limit
ELSE 0
END) AS no_scholarship_places
FROM course;
The query above counts two SUMs: the total number of spots available in all courses that grant scholarships and the total number of places available in those that do not.
We have a CASE WHEN statement inside SUM. We add the value from the column place_limit to the total named scholarship_places when the value in the column scholarship is true. We add 0 else. In the same fashion, the other SUM is computed.
SQL Case using Date
Receive session information, but just for the current day. Sessions must be organised by their start time in descending order. We'll need an ascending sequence for the rest of the days.
The column alias should be inserted after the CASE expression's END clause, as the date format string is incorrect.
Example 1: You could try the following to achieve the result:
desc info
Name Null? Type
----------------------------------------- -------- ----------------------------
START_DATE DATE
END_DATE DATE
AMOUNT NUMBER
select * from info;
Output:
START_DAT END_DATE AMOUNT
--------- --------- ----------
30-JUN-16 14-SEP-16 1200
14-SEP-16 30-JUN-16 1300
30-MAY-16 30-JUN-16 1300
14-SEP-16 20-JUL-16 1400
select start_date,
case
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06-30-2016', 'MM/DD/YYYY') end as end_date, amount from info
order by end_date asc; 2 3 4
Output:
START_DAT END_DATE AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16 1200
14-SEP-16 30-JUN-16 1400
30-MAY-16 1300
14-SEP-16 1300
Example 2: To solve this task, we can use CASE logic and using DATE in the ORDER BY clause and get the desired result easily:
USE TestDB
GO
SELECT *
FROM [Session]
ORDER BY CASE WHEN DATEDIFF(DAY, CAST(StartTime AS Date),CAST(N'2019-11-28' AS Date))=0 THEN StartTime END DESC,
CASE WHEN DATEDIFF(DAY, CAST(StartTime AS Date),CAST(N'2019-11-28' AS Date))>0 THEN StartTime END ASC
SQL Case When Null
The query returns the column 'Databasename' from the derived list values clause to evaluate Null using the two Case expressions; example 1 has a value of Null, and example 2 has a value of 'SQLUndercover,' as shown below:
When a statement is fulfilled, the program will cease reading and return the result. If none of the requirements are met, the value in the ELSE clause is returned. It returns NULL if there is no ELSE component and no criteria are met.
Example 1:
SELECT Databasename,
CASE Databasename
WHEN NULL THEN 'It''s a NULL'
WHEN 'SQLUndercover' THEN 'Match Found'
ELSE 'No match Found'
END AS CaseExample1,
CASE
WHEN Databasename IS NULL THEN 'It''s a NULL'
WHEN Databasename = 'SQLUndercover' THEN 'Match Found'
ELSE 'No match Found'
END AS CaseExample2
FROM (VALUES(NULL)) AS DerivedList (Databasename)
Example 2: If the state is not NULL, the CASE expression is used to sort customers by states; if the state is NULL, the CASE expression is used to sort customers by country:
SELECT customerName,
state,
country
FROM
customers
ORDER BY (
CASE
WHEN state IS NULL
THEN country
ELSE state
END);
SQL Case When Then 1 Else 0
The SUM(CASE WHEN x THEN 1 ELSE 0) used for multiple columns.
The WHERE clause in PostgreSQL contains a CASE statement. CASE expressions can be used anywhere where two values are compared, such as SELECT, WHERE, GROUP BY, and HAVING clauses. So let's look at a CASE statement in the WHERE clause as an example.
Example 1: PostgreSQL CASE expression in WHERE clause:
SELECT *
FROM Employee
WHERE Emp_ID = CASE WHEN Dept='IT' THEN 1
ELSE 0
END;
In this example, we have compared the Emp_ID with the value generated by the CASE expression to find the employee whose department is ‘ IT ‘ and its Emp_ID is ‘ 1 ‘.
Example 2: To illustrate how I can achieve the same result as when I use simply WHERE, I'll use the CASE statement inside of COUNT.
SELECT COUNT(email)
FROM users
WHERE email LIKE ‘%.com’;
And 1000 results by using this:
SELECT COUNT(
CASE
WHEN email LIKE ‘%.com’ THEN 1 ELSE 0 END)
FROM users;
Example 3: I'm looking to see if there is a better approach to the query below. What I'm trying to do is create a summary report, compiling stats by date.
SELECT CAST(Detail.ReceiptDate AS DATE) AS 'DATE'
, SUM(CASE WHEN Detail.Type = 'TotalMailed' THEN 1 ELSE 0 END) AS 'TOTALMAILED'
, SUM(CASE WHEN Detail.Type = 'TotalReturnMail' THEN 1 ELSE 0 END) AS 'TOTALUNDELINOTICESRECEIVED'
, SUM(CASE WHEN Detail.Type = 'TraceReturnedMail' THEN 1 ELSE 0 END) AS 'TRACEUNDELNOTICESRECEIVED'
FROM
(
select SentDate AS 'ReceiptDate', 'TotalMailed' AS 'Type'
from MailDataExtract
where sentdate is not null
UNION ALL
select MDE.ReturnMailDate AS 'ReceiptDate', 'TotalReturnMail' AS 'Type'
from MailDataExtract MDE
where MDE.ReturnMailDate is not null
UNION ALL
select MDE.ReturnMailDate AS 'ReceiptDate', 'TraceReturnedMail' AS 'Type'
from MailDataExtract MDE
inner join DTSharedData.dbo.ScanData SD ON SD.ScanDataID = MDE.ReturnScanDataID
where MDE.ReturnMailDate is not null AND SD.ReturnMailTypeID = 1
) AS Detail
GROUP BY CAST(Detail.ReceiptDate AS DATE)
ORDER BY 1
SQL Case When Then Select
Boolean expressions or comparison operators are used in the Searched Case expression.
Only one value is returned by the subqueries.
The abbreviated version of CASE compares a set of data against an expression (often a column). This version is a little shorter and eliminates the need to repeatedly evaluate the evaluated expression. The ELSE clause is still available.
The case statement returns scalar values when there is an outside comparison such as = or >.
Syntax:
The general syntax of the searched CASE expression is:
CASE
WHEN condition_1 THEN result_1
[WHEN condition_2 THEN result_2]
[..................]
[WHEN condition_n THEN result_n]
[ELSE expression]
[else result_n]
END
Example 1: A scalar value is "equivalent" to a row with one column and one value. As a result, the following would be permitted:
where col = (CASE WHEN date > '2014-02-28' THEN (SELECT max(col2) FROM TABLEC WHERE...)
ELSE (SELECT min(col3) FROM TABLE B WHERE...)
END)
Example 2:
SELECT Id, ItemId, Price,
CASE Price WHEN 5 THEN 'CHEAP'
WHEN 15 THEN 'AFFORDABLE'
ELSE 'EXPENSIVE'
END as PriceRating
FROM ItemSales
A word of warning: When utilising the short option, keep in mind that the complete statement is evaluated at each WHEN. As a result, the following statement:
SELECT
CASE ABS(CHECKSUM(NEWID())) % 4
WHEN 0 THEN 'Dr'
WHEN 1 THEN 'Master'
WHEN 2 THEN 'Mr'
WHEN 3 THEN 'Mrs'
END
SQL case with alias
In SQL, the Case statement is typically used when equality expressions are present. To change the output, the SQL Case statement is commonly placed inside of a Select list. Another option is to put the alias name first, followed by equals SQL Case.
MySql and MsSql will not allow this because they will try to match all columns in the CASE clause to tables in the WHERE clause with the same ALIAS name.
Example 1: Inside the same query, column alias references are not enabled; alternatively, you should create a subquery:
SELECT
q.*,
CASE WHEN q.flag = 1 THEN 'Y' END AS COL2
FROM (
SELECT
F AS flag
FROM table
) AS q
Alternatively, if the alias is simple, you can utilize the column directly instead of the alias, as illustrated below.
SELECT
F AS flag,
CASE WHEN F = 1 THEN 'Y' END AS COL2
FROM table
NOTE: The column alias resolution is presently not handled by the Roboquery converter. It must be completed manually.
Example 2: alias a column name and then use that in a CASE statement:
SELECT col1 as a, CASE WHEN a = 'test' THEN 'yes' END as value FROM table;
I'm trying to alias the column since my CASE statement will be generated programmatically, and I'd like the column that the case statement utilizes to be stated in the SQL rather than supplying additional input to the program.
Example 3: I don't know what DBMS you are talking about, but I guess you could do something like this in any DBMS:
SELECT *, CASE WHEN a = 'test' THEN 'yes' END as value FROM (
SELECT col1 as a FROM table
) q
SQL Case Groupby
In SQL, the case statement returns a value based on a condition. In select queries, we can utilise a Case statement in conjunction with the Where, Order By, and Group By clauses. It can also be used in an Insert statement.
When copying/pasting the full CASE statement into the GROUP BY clause, omit the AS year group column naming:
Example 1: If a set of data is required by different organisations inside the company in slightly different contexts. This, like many of my example queries, should be written better. It, like many of my sample queries, reflects what I observe in the wild (and for those keeping track at home, I'm now testing with the WideWorldImporters database):
CREATE PROCEDURE dbo.InvoiceGrouping (@x INT)
AS
SELECT SUM(il.UnitPrice),
COUNT(i.ContactPersonID),
COUNT(i.AccountsPersonID),
COUNT(i.SalespersonPersonID)
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS il
ON il.InvoiceID = i.InvoiceID
GROUP BY CASE WHEN @x = 7 THEN i.ContactPersonID
WHEN @x = 15 THEN i.AccountsPersonID
ELSE i.SalespersonPersonID
END;
GO
Depending on the column used in the GROUP BY, if you run this for any value above 7, 15, or other, you'll receive the same execution plan. Parameter Sniffing, on the other hand, is still a factor. You only get 10 rows back when you group this data by SalesPersonID. If a parameter value other than 7 or 15 is provided, this will be displayed as the estimated number of rows returned.
Example 2: In this article, we would explore the CASE statement and its various use cases.
SELECT CASE WHEN year = 'FR' THEN 'FR'
WHEN year = 'SO' THEN 'SO'
WHEN year = 'JR' THEN 'JR'
WHEN year = 'SR' THEN 'SR'
ELSE 'No Year Data' END AS year_group,
COUNT(1) AS count
FROM benn.college_football_players
GROUP BY CASE WHEN year = 'FR' THEN 'FR'
WHEN year = 'SO' THEN 'SO'
WHEN year = 'JR' THEN 'JR'
WHEN year = 'SR' THEN 'SR'
ELSE 'No Year Data' END
Example 3: Anyway, the good news is that we can make summary groups with the CASE expression we've created. The data is grouped by PriceRange in the following SQL. The minimum, maximum, and average ListPrice data are calculated.
SELECT
CASE WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange,
Min(ListPrice) as MinPrice,
Max(ListPrice) as MaxPrice,
AVG(ListPrice) as AvgPrice,
Count(ListPrice) as NumberOfProducts
FROM Production.Product
GROUP BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END
ORDER BY MinPrice
We can't utilize the column alias PriceRange in the GROUP BY clause, unlike the ORDER BY clause. It's necessary to repeat the complete CASE phrase.
Example 4: To compute the overall count of products such as "SQL" and "Oracle," use the query below, which includes a Group By clause and a CASE statement:
SELECT
CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
ELSE NULL END Products ,
SUM(Count) OverallCount
FROM @Tmp
GROUP BY CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
ELSE NULL END
SQL Case with Join
Example 1: A CASE expression returns a value from the THEN portion of the clause and it join the case with some conditions:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
ELSE 0
END = 1
Example 2: A CASE expression returns a value from the THEN portion of the clause.
Relationship between sys.partitions and sys.allocation_units depends on the value of sys.allocation_units.type. So to join them together with case statement I would write something similar to this:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
ELSE 0
END = 1
SQL Case with Not In
This SQL example uses a select query with a where clause. Now I need to add a condition depending on user access to the where clause. If the user does not have access, an extra condition must be added to the where clause; otherwise, if the user has access, no more logic is required.
Example 1: You can use CASE in WHERE as Shown below:
WHERE 1=(
CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
ELSE 0 END
)
this will not return any row if @x=1 and @y=1. If you want to return all rows if @x=1 and @y=1
WHERE 1=(
CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
WHEN @x = 1 and @y = 1 THEN 1
ELSE 0 END
)
Example 2:
SELECT * FROM mytransactions m
WHERE mytransactions.id NOT IN
CASE WHEN @OnlyNonExported = 0
THEN (SELECT -1)
ELSE (SELECT id FROM trackedtransactions)
END
However, SQL does not appreciate this syntax, and it turns out that you can only employ = or != conditions in a WHERE clause when using a CASE statement.
SQL Case with Select Inside
Example 1: SELECT option used inside CASE WHEN clause:
SELECT (CASE WHEN ISNULL(s.rate, 0) = 0
THEN (SELECT TOP 1 pr.rate
FROM ProjectRates pr
WHERE (pr.projectID = p.ID) AND (pr.effectiveDate < GETDATE())
ORDER BY pr.effectiveDate DESC
)
ELSE (SELECT TOP 1 sr.rate
FROM ShiftRates sr
WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate < GETDATE())
ORDER BY pr.effectiveDate DESC
) --s.rate
END) AS rate
FROM Projects p INNER JOIN
Shifts s
ON p.ID = s.projectID
WHERE p.ID = @projectID;
Example 2: Select statment with in a CASE Statement:
SrTeamManagerDate
,'TeamMentor' =
CASE
WHEN TeamMentorDate IS NOT NULL THEN 'Team Mentor'
WHEN (SELECT r.AchieveTitle FROM Repromotes r WHERE RepFlag = 'X' AND AchieveTitle = 'Team Mentor) THEN 'Team Mentor*'
ELSE NULL
END
SQL Case with Substring
Example 1: For SUBSTRING with CASE statement:
DECLARE @tmp TABLE (FullFileName VARCHAR(100))
INSERT @tmp SELECT 'File_2013-04-21.txt'
INSERT @tmp SELECT 'File_2013-04-21.txt -1'
INSERT @tmp SELECT 'File_2013-04-21.txt -2'
SELECT FullFileName,
SUBSTRING(FullFileName,1,
CASE WHEN (CHARINDEX(' -',FullFileName)) > 0
THEN CHARINDEX(' -',FullFileName)
ELSE LEN(FullFileName) END ) [FileName]
,CASE WHEN CHARINDEX(' -',SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))) > 0
THEN SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))
ELSE NULL END Filesuffix
FROM @tmp
Example 2: CASE statement with SUBSTRING:
SELECT * FROM (
SELECT
CASE
WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN 'A'
WHEN COL LIKE '%9' THEN 'H'
WHEN SUBSTRING(COL,2,1) = '4' THEN SUBSTRING(COL,1)
END Letter,
CASE
WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN substring(col FROM 2 for 1)
WHEN COL LIKE '%9' THEN '9'
WHEN SUBSTRING(COL,2,1) = '4' THEN '7'
END Number
FROM A
)
WHERE (Letter ~ '^[A-Za-z]$') = true AND (Number ~ '^[0-9]$') = true;
SQL Case Union
You might be able to use a CASE statement instead of a UNION when combining data from different searches.
Example: This simple SQL statement combines the results of three queries against the table using UNION.
select t1.itemcode, t1.y1qty, t1.yr2qty,
CASE
WHEN y1qty = 0 AND yr2qty = 0
THEN blah blah
WHEN y1qty = 0 and y2qty >
THEN blah blah
ELSE blah blah
END AS Diff
FROM
(
SELECT a.itemcode,
SUM(b.quantity) AS 'y1qty',
ISNULL(NULL,0) AS 'yr2qty',
SUM(c.amt) AS 'yr1amt'
ISNULL(null,0) AS 'yr2amt',
FROM ABC a
LEFT JOIN DEF b ON a.itemcode = b.itemcode
GROUP BY a.itemcode) t1
UNION
select t2.itemcode, t2.y1qty, t2.yr2qty,
CASE
WHEN y1qty = 0 AND yr2qty = 0
THEN blah blah
WHEN y1qty = 0 and y2qty >
THEN blah blah
ELSE blah blah
END AS Diff
FROM
(
SELECT a.itemcode,
ISNULL(NULL,0) AS 'yr1qty',
SUM(b.quantity) AS 'yr2qty',
ISNULL(NULL,0) AS 'yr1amt',
SUM(c.amt) AS 'yr2amt'
FROM ABC a
LEFT JOIN DEF b ON a.itemcode = b.itemcode
GROUP BY a.itemcode
) t2
SQL Case with Where Clause
Use a CASE Statement whenever a valid expression is used in the SELECT statement, like the filter criteria in the WHERE clause.
In the SELECT clause, we can utilize the Case Statement, as most DBAs and developers do. The Case Statement can also be used in the WHERE clause.
The value is returned by the CASE statement based on the condition. In the Where, Order by, and Group by clauses, we can utilize a case statement.
Example 1: A Case Statement is useful for selecting a value from a list of possible values. Consider the case if we wished to base the rental_duration on the rental_rate. We could use a Case Statement to accomplish this:
SELECT film_id, title, rental_rate, rental_duration
FROM film
WHERE rental_duration = CASE rental_rate
WHEN 0.99 THEN 3
WHEN 2.99 THEN 4
WHEN 4.99 THEN 5
ELSE 6
END
ORDER BY title DESC;
Example 2: Let's say we want to get all people with the persontypes VC or IN from the Persons table. You might use CASE to accomplish this:
SELECT FirstName, LastName, PersonType
FROM Person.Person
WHERE 1 = CASE
WHEN PersonType = 'VC' THEN 1
WHEN PersonType = 'IN' THEN 1
ELSE 0
END
PersonType will be tested for either VC or IN in this case. Return a 1 if it matches the CASE WHEN. The row is included in the result after the filter matches.
Example 3: If the first name is 'RAM' or the last name is 'SHARMA,' I want to show the salary in the Customer table:
SELECT *
FROM [dbo].[Customer]
WHERE [Salary] =
CASE
WHEN [First_Name] = ‘RAM’ THEN [Salary]
WHEN [Last_Name] = ‘SHARMA’ THEN [Salary]
ELSE NULL
END
The salary of the customer with the first name Ram or the last name Sharma is presented in the Customer table.
The result is displayed by utilising a CASE statement with the where condition.