#sql case

The definition of the word “case”  in Dictionary  is “a set of circumstances or conditions” or  “an instance of a particular situation” or “occurrence of a particular kind or category”. I think the case statement in SQL closely resembles the last definition ““occurrence of a particular kind or category”.

Let’s take the simple case of Customer Spend. You are starting a marketing campaign post Christmas and would like to give a discount to your customers based on the spending this year.

You want to give 20% discount if the customer has spent $40+, 15% to those who spent between $20 and $40 and 10% to those who spent between $10 and $20 and 5% to the rest of the customers who may have spent less than $10 or nothing at all. Now let’s write the query to see which brackets your customers fall into.

In the SQL statement you will notice the “Else 5” for all customers who have either spend less than $10 or spend nothing. Now note the NULL value in Sale column. These accounts have signed up but have not spend a dollar yet and thus gets a 5% discount. Another important thing you need to note in this query is the LEFT JOIN. We used LEFT JOIN because we want to send discount to all Accounts/Customers whether they have spend any dollar. Left join includes all account from the Customer and displays null if the customer has not spend anything yet. If we had used INNER JOIN instead of LEFT, we would have got a list of accounts that have already spend, i.e. have an entry in the Orders tables as well.

Here is what we will get if we had used INNER JOIN instead.

I will conclude this article with a few “need to know” statements before we move onto to tracking/analyzing Marketing Campaigns. Comparison Operators When we are looking for a specific set of records based on a certain condition, we use the Where clause along with a operator like =, > etc. Some of these operators like =,IN, <> will work for both numerical(Age, Sale etc) and string/text values (name,city,state etc. columns). In case of string comparison,  we wrap the comparing string within single quotes. Here are a few examples.

The IN clause is a replacement of one or more “OR” clause. Here is an example  that shows how they return the same records.

Execute the following statements and see what you get. SELECT * FROM [Customers] Where age !=30

SELECT * FROM [Customers] Where age >30

SELECT * FROM [Customers] Where State<> 'CA'

To compare a part of a text, we use LIKE as shown below. The percent sign (%) stands for any character(s). The first statement returns all records where LastName ends with “on” and we get Ruxton,Wixon,Johnson,Pon.

The second statement returns all records where LastName starts with “p” and we get Pon, Perez, Paliska. The last statement returns all records that has letters “an” in the beginning or at the end or in the middle and we get Coleman, Wilman, Fang and Chang.

Most of these numerical operators are also used in Having clause  along with an aggregate function like count or sum like this.

We can also use BETWEEN to get records within a certain date range or between two numbers.

You can also filter rows based on NULL values. If you want to find out all the customers who have signed up but has not spend any money, you will use the following query.