This website uses cookies to ensure you get the best possible experience. See our Cookies Policy.

PMG Digital Made for Humans

Making a case for SQL Case Statements

3 MINUTE READ | May 9, 2017

Making a case for SQL Case Statements

The CASE statement is SQL’s method of handling your standard IF/THEN logic in Excel. You begin with CASE, followed by at least one pair of WHEN and THEN statements, and then you END it. You can be exhaustive in your WHEN/THEN statements, accounting for every possibility, or you can also use the ‘else’ functionality, and create a default value if the column you are looking at does not match any of your WHEN statements.

What’s the use case, no pun intended, for using case statements? I’m glad you asked. Anyone who works with data knows that it often needs a bit of cleaning-up or standardization before it is ready for use, and that’s where case statements can really come in handy.

Take this dataset for example. It breaks down revenue and bids for each campaign and device.

If I needed to pull mobile and desktop revenue data for each campaign (assuming in this situation you consider tablets and iphones are both considered mobile) I could pull this into excel, filter, do some sumifs, etc, and add all of my iphone & tablet data points to get total mobile revenue. Or with a simple case statement, I could easily re-classify iphones and tablets as mobile, and have SQL do all the addition for me. Much easier, much quicker, and no room for user error.

Assuming iphone, tablet, and desktop are my only 3 device options, we essentially need a case statement that looks at the device column and says “if I find “iphone” or “tablet” in device, put ‘mobile’ in my new column labeled “mob_device”, and if it’s not one of those, just copy device over to the new column”.  There’s more than one way to accomplish this, but here’s what that could look like:

You can see in the new column created by the case statement that both iphones and tablets are now labeled “mobile”. When the boolean expression evaluates to true, the new value ‘mobile’ is placed in the mob_device column. If it evaluates to false, the else statement takes care of it and places whatever value it already has in the device column into the mob_device column.  I included both columns in my select statement, but there’s really no need to include the device column at this point. To find out exactly what my revenue is for each mobile or desktop campaign, all I need to do is a simple sum().

There are more simplistic methods for using a case statement, but evaluating a boolean expression (in this case “device = ‘tablet'” as an example) in the WHEN gives you a lot of free reign. You aren’t just limited to testing equality, does A = b ?, you can compare using operators like greater than (>) or less than (<). Is A > B, is A < B, etc.

Using this same data source as an example, if I wanted to increase bids for all campaigns where revenue was greater than $100, my case statement might look something like this.

Insights meet inbox

Sign up for weekly articles & resources.

So as you can see, there’s really nothing too fancy about case statements, but when properly implemented they can be a huge time-saver and well worth your while. Next time you’re facing a messy dataset, breath deeply and consider bringing in a few case statements to help get your data in line.


Posted by Jamie Reinhard

Related Content

thumbnail image

Get Informed

PMG’s Predictive Dashboard Wins Innovation Award

1 MINUTE READ | September 28, 2021

Get Informed

Why Plugins Are a Double-Edged Sword for Marketers

8 MINUTES READ | September 14, 2020

thumbnail image

Get Inspired

Maximizing Success with Visual Search

7 MINUTES READ | December 2, 2019

Get Informed

Applying Function Options to Domain Entities in Go

11 MINUTES READ | October 21, 2019

Get Informed

How to Win With Google’s Shopping Showcase Ads

2 MINUTES READ | September 20, 2019

thumbnail image

Get Inspired

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

Get Informed

PMG Shortlisted for US Search Award

1 MINUTE READ | August 1, 2019

Get Inspired

Why You Should Learn SQL

4 MINUTES READ | July 5, 2019

thumbnail image

Get Inspired

3 Tips for Showing Value in the Tech You Build

5 MINUTES READ | April 24, 2019

Get Informed

Testing React

13 MINUTES READ | March 12, 2019

Get Informed

A Beginner’s Experience with Terraform

4 MINUTES READ | December 20, 2018

All POST