• About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
  • About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
May 09, 2017

Making a case for SQL Case Statements

Posted by Jamie Reinhard

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.

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.

dataSQL
Previous
Next

Latest White Papers

  • Shifting Plans for 2020 & Beyond
  • Game On: How Brands Can Log Into A Diverse Multi-Billion Dollar Industry
  • What CCPA Means For Brands
  • How Google is Improving Consumer Data Privacy
  • Ways to Prepare for the Cookieless Future
  • See all White Papers

Featured Posts

  • Ad Age Names PMG #1 Best Place to Work in 2021
  • MediaPost Names PMG Independent Agency of the Year
  • PMG Client Portfolio Trends During Amazon Prime Day 2020
  • A Closer Look at the Congressional Big Tech Market Power Report
  • What to Know About Reddit

Categories

  • Consumer Insights
  • Content
  • Creative Design
  • Data Analytics
  • Development
  • Digital TV & Video
  • Ecommerce
  • Industry News
  • Local
  • Mobile
  • Paid Search
  • PMG Culture
  • Programmatic & Display
  • SEO
  • Social Media
  • Structured Data
Fort Worth

2845 West 7th Street
Fort Worth, TX 76107

Dallas

3102 Oak Lawn Avenue
Suite 650
Dallas, TX 75219

Austin

823 Congress Avenue
Suite 800
Austin, TX 78701

London

33 Broadwick Street
London
W1F 0DQ

New York

120 East 23rd Street
New York, NY 10010

Get in touch

(817) 420 9970
info@pmg.com

Subscribe to the PMG Newsletter
© 2021 PMG Worldwide, LLC, All Rights Reserved
  • Contact
  • Privacy Policy
 Tweet
 Share
 Tweet
 Share
 Tweet
 Share
 LinkedIn
We and our partners use cookies to personalize content, analyze traffic, and deliver ads. By using our website, you agree to the use of cookies as described in our Cookie Policy.