Skip to content
  • YouTube
  • FaceBook
  • Twitter
  • Instagram

Data Analytics Ireland

Data Analytics and Video Tutorials

  • Home
  • Contact
  • About Us
    • Latest
    • Write for us
    • Learn more information about our website
  • Useful Links
  • Glossary
  • All Categories
  • Faq
  • Livestream
  • Toggle search form
  • how to write subqueries in SQL SQL
  • R Tutorial: How to pass data between functions R Programming
  • How to use zip() function in Python Python
  • How To Pass Data Between Functions Python Functions
  • how do I declare a null value in python? exception handling
  • TypeError: Array() Argument 1 Must Be A Unicode Character, Not List array
  • ValueError: cannot convert float NaN to integer Null values
  • hide a column from a data frame Python Dataframe

how to use case statements in SQL

Posted on February 6, 2021March 5, 2021 By admin

In recent posts on SQL here on this website, we have covered off a number of ways to extract your data from wildcards to filters.

Here we are going to build on that learning, but bring in a new dimension in the use of case statements.

These are very handy to use when you may want to apply specific data values to a piece of data.

We have several posts on machine learning; in effect, this could be used to add data needed to run the model.

Lets start looking at the SQLite database we have been using for other posts.

The table we will work off is shown below, we have used in other posts.

What we are going to look at, is the column name, and assign a binary number, 1 or 0 if the string in this column begins with the letter “A”.

Case statements work on the basis when a value meets certain criteria.

They normally are constructed as follows:

CASE

… criteria you want to meet

End

The power of this is that you are starting to add additional attributes about the string in a new column.

An example could be if a column value is the incorrect length, can be used as a data quality check. See my example below.

Below in the output when we apply a value of 1 to a new column if it starts with “A”.

Important to remember that the new column country_check only shows in the output.

On the above we have just one case statements, but what if we want more than one in the query?

If we want to apply multiple case statements to a query, just add a new when line, with your criteria.

The wild card values below will return any value between 1-5, they can be any combination of 1-5.

What if we want to have multiple case statements on different columns?

As can be seen below we have selected two columns to apply our case statement to.

We just have added a check on the column iso_code.

As before the new column will not be saved to the database.

The video to show how this is applied can be found below:

In conclusion we have shown examples of how to apply case statements in SQL, with the following pointers:

  • Multiple case statements can be created.
  • Wildcards can be used to return different variations of your data.
  • Outputs are populated into a new column, though this is not saved to the table.
  • You can test for multiple scenarios.
  • Using case statements allows you to understand your data better with additional attributes.

We have lots of posts on this website that will help you build your data analytics skills.

SQL, SQLite Tags:binary, case statements, Data Quality, SQL case statements, sql filtering, sql where, SQL wildcards

Post navigation

Previous Post: How to use wildcards in SQL
Next Post: how to create and drop a table in SQL

Related Posts

  • select rows with a certain value using SQL SQL
  • What is Apache Spark and what is it used for? Apache Spark
  • What is Data Integrity? SQL
  • What is Query Optimization in SQL? SQL
  • What is the difference between CHAR and VARCHAR2 in SQL? SQL
  • create read update delete using Tkinter class

Select your language!

  • हिंदी
  • Español
  • Português
  • Français
  • Italiano
  • Deutsch
  • How to check if a file is empty Python
  • What is the difference between DROP and TRUNCATE in SQL? SQL
  • TypeError: List Indices Must Be Integers Or Slices, Not Tuple exceptions
  • how to compare two lists in Python Python Lists
  • What are Lambda functions in Python? Python
  • ValueError: pattern contains no capture groups Value Error
  • What is The Julia Programming Language Julia programming
  • How to use zip() function in Python Python

Copyright © 2023 Data Analytics Ireland.

Powered by PressBook Premium theme

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT