Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Using the CASE Expression in SQL Queries

Make the most of SQL in development projects

The DBMS (database management system) is often faster at performing calculations like summing up or averaging the values in a column of numbers than, for instance, the application server. However, there are many cases in which performing the same calculation on all of the data in a column will not provide the correct result.

This article will show you how to use the CASE expression in SQL queries to more flexibly perform aggregate calculations and set values based on a logical statement.

An example of the need to apply the CASE expression in an aggregate function is an online shopping cart with several item-specific options that, depending on the options chosen, change the way an item's price is added to the cart and, therefore, the final total price of the order. I will discuss a shopping cart for an online store that requires variable pricing within four option areas. The price calculation of the final order will vary depending on the setting of each option.

The example store sells items including artwork, books, gifts, events, and memberships. It's a dynamic store allowing administrators to add and delete categories, subcategories, and items. One item can exist in as many categories as the administrator would like. Individual items within a category may be subject to different pricing options. With these requirements, the programmer does not know what categories and subcategories will exist in the store in the future or under what category or pricing options an item will be placed. Therefore, options must be set at the item level and be available for administrators to set and change.

Requirements for the example store include the ability of administrators to choose options in these four areas:

  1. Sale price: Individual items may or may not be on sale. The database table contains a column for regular price and sale price and an additional flag column named SaleItem, allowing the administrator simply to change the SaleItem setting rather than change the value for regular price each time the item goes on sale.
  2. Shipping: Shipping cost is calculated based on the shopping cart total. However, administrators can select certain items such as memberships so that they do not carry a shipping charge.
  3. Member and employee discount: The organization has members and employees who are entitled to different discounts on purchases from the store. However, some items do not qualify for discounting under any circumstance.
  4. Sales taxes: Some items are taxed and some are not. For example, tax law does not require sales tax on memberships.

Overview of the SUM Function
Programmers often use the SUM() aggregate function to total the numbers contained in a database column. For instance, in a shopping cart, you might use the sum function as follows:

SELECT Name, SUM(Price) as Total
FROM Item
WHERE ItemID=#SomeValue#

The database will return rows of records, each containing the Name of the item and the sum total of the values found in the Price column for the records selected. Each row will contain the same value for Total. If no columns are selected, the query will return one row with the value of Total.

Based on the discussion above, simply totaling the prices in the Price column will not work for this shopping cart. For example, if the customer chooses an item that's on sale, the price in the SalePrice column must be used for that item. You might choose to solve this problem with CFML as follows:

<CFSET SumPrice=0>
<CFLOOP QUERY="GetCartItems">
<CFIF SaleItem is 1>
<CFSET SumPrice=SumPrice+(Quantity*SalePrice)>
<CFELSE>
<CFSET SumPrice=SumPrice+(Quantity*Price)>
</CFIF>
</CFLOOP>

This solution does not rely on SQL to total the item prices. Instead, the application loops through all of the items selected and adds them up in a CFLOOP loop. However, SQL does provide an elegant way to perform the same calculation using the SUM aggregate function in combination with the CASE expression. The total price of the items, taking into account those that are on sale, is included in the returned table using this method.

Overview of the CASE Expression
The CASE expression, introduced in SQL-92, provides the programmer with the ability to choose one of multiple values based on a logical expression. The value added to the item total of our shopping cart using the SUM() aggregate function is the value in the Price column for an item that is not on sale and the value in the SalePrice column for an item that is on sale. The CASE expression is supported by SQL Server, MySQL, PostgreSQL, and Oracle beginning with Oracle8i.

The SQL query to total the price of the items, taking into account those that are on sale, looks like:

SELECT
SUM(CASE WHEN SaleItem=1 THEN SalePrice ELSE Price END)
FROM Item
as TotalPrice
WHERE Item.CartID=#SomeValue#

In the query above, the item is evaluated to see if SaleItem is 1. If it is, the SalePrice column is used for that item in calculating the total price. If not, the Price column is used.

Database Tables
The critical database tables for our cart include Item, Cart, CartItems, Tax, Discount, and Shipping.

The important columns in each table are:

  1. Item: ItemID (primary key), Price, SalePrice, SaleItem (1 or 0), Shipping (1 or 0), Discount (1 or 0), Tax (1 or 0)
  2. Cart: CartID (primary key)
  3. CartItems: CartItemID(primary key), CartID, ItemID, Quantity
  4. Tax: Pct, Gov
  5. Discount: DiscountID (primary key), Name, Pct
  6. Shipping: MinTotal, MaxTotal, ShipCost

The columns SaleItem, Shipping, Discount, and Tax, in the Item table, are switches used to turn each of the item pricing options on and off. For instance, if SaleItem is 1, then the product will be priced at the sale price. Of course, there are a number of other columns in the Item table that contain the content required to display the item.

The Cart table stores keys to identify carts and to find items in a cart. A Cart record is created when a customer adds his first item to a cart. The CartID is stored to allow for the retrieval of cart items after the cart session has expired.

The CartItems table allows for the joining of the cart with the items in the cart. It also stores the quantity of the item to be included in the cart.

The Tax table stores the abbreviated names and the percent sales tax for each government requiring the addition of tax.

The Discount table stores the names of member or employee discounts and their respective discount percentage.

The Shipping table stores min/max ranges of item totals and the corresponding shipping charge to be applied to the order based on the order total falling between or being equal to the min and/or max total.

Shopping Cart Calculation Example
Before summing the cart for the customer prior to final checkout using the SUM() aggregate function and CASE expression, information necessary for the calculation of the cart total is retrieved from the database as follows.

First, get the discount percentage based on the member type (individual, student, etc.) or employee status selected by the customer in the checkout form. Optionally, if a member and/or employee database exists, you can verify the member type or employee status from the employee or member records.

<CFQUERY NAME="GetDiscount" DATASOURCE="#Application.Datasource#">
SELECT Name, Pct
FROM Discount
WHERE DiscountID='#Form.DiscountID#'
</CFQUERY>

Next, get the sales tax based on the state to which the order will be shipped. The customer has provided a "bill to" address and, optionally, a "ship to" address. Sales tax is based on the "ship to" state. If the "bill to" address is the same as the "ship to" address, the customer completes only the "bill to" address form. At this point, check to see if the form field Form.sState (the "ship to" state) is not available or is null. If either is true we use Form.State (the "bill to" state) to calculate sales taxes because the "ship to" address and the "bill to" address are the same. If both are false, use the "ship to" state.

<CFIF Not IsDefined('Form.sState') or Form.sState is "">
<CFSET ShippingState=Form.State>
<CFELSE>
<CFSET ShippingState=Form.sState>
</CFIF>
<CFQUERY NAME="GetSalesTaxes" DATASOURCE="#Application.Datasource#">
SELECT Pct
FROM Tax
WHERE Gov = '#ShippingState#'
</CFQUERY>

Finally, check to see if sales tax applies. If there are no sales tax records for the state to which the order will be shipped, GetSalesTaxes.RecordCount will be 0 and sales tax will not be added.

<CFIF GetSalesTaxes.RecordCount gte 1 and GetSalesTaxes.Pct is not "">
<CFSET TaxRate=GetSalesTaxes.Pct>
<CFELSE>
<CFSET TaxRate=0>
</CFIF>

Now it is known what discount to apply and what percentage of the total price of the taxable items will be added to the bill for sales tax. The real work using the CASE expression can begin.

Elsewhere in the application when the customer places his or her first item into the cart, Session.Cart is created and set to a unique value generated by the CreateUUID() function. Because records in the CartItems table contain the field CartID that is set to a value equal to Session.Cart as items are added to the cart by the shopper, using Session.Cart directly in the query GetCartItems detailed below works as long as Session.Cart exists.

However, Session.Cart is deleted after final checkout to end the cart session. In order to re-use the following code to process orders after the cart session is over and Session.Cart no longer exists, or to extend the life of the cart beyond the existence of Session.Cart if desired, create CartSumCartID and use it in the GetCartItems query in place of Session.Cart. This allows the value of CartSumCartID to be set using a database record instead of Session.Cart (e.g., the value in the CartID field in the Cart table described in the Database Tables section above). For now, just set CartSumCartID to Session.Cart because in this example the query GetCartItems is being used prior to final checkout.

<CFSET CartSumCartID=Session.Cart>

The query at the heart of the shopping cart is as follows:

<!---begin query to retrieve the cart items and the shopping cart totals needed to generate the cart final total--->

<CFQUERY NAME="GetCartItems" DATASOURCE="#Application.Datasource#">
SELECT CartItems.CartItemID, CartItems.Quantity, Item.*,

  • The following subquery calculates the value for TotalPrice.
  • Join Item and CartItems to find items contained in the cart.
  • If it is a sale item use the sale price, if not use the price.

(SELECT
SUM(CASE
WHEN SaleItem=1
THEN Quantity*SalePrice
ELSE Quantity*Price
END) FROM (Item INNER JOIN CartItems ON Item.ItemID = CartItems.ItemID)
WHERE CartItems.CartID='#CartSumCartID#') as TotalPrice,

  • To calculate the total after discounting. (TotalDiscountPrice) use the following nested CASE expressions in the subquery below.
  • If it is a sale item use the sale price, if not use the price.
  • If discounting is allowed for this item multiply total item price (quantity*price) by 1-Discount; otherwise, use the total item price without discount.

(SELECT
SUM(CASE
WHEN SaleItem=1
THEN CASE WHEN Discount=1 THEN Quantity*SalePrice*(1-#Discount#) ELSE Quantity*SalePrice
END
ELSE CASE WHEN Discount=1 THEN Quantity*Price*(1-#Discount#) ELSE Quantity*Price END
END) FROM (Item INNER JOIN CartItems ON Item.ItemID = CartItems.ItemID)
WHERE CartItems.CartID='#CartSumCartID#') as TotalDiscountPrice,

  • Next calculate SalesTax.
  • TaxRate was set above.
  • Apply sale prices and discounts before calculating sales tax.
  • If the item is nontaxable, 0 is added to SalesTax.

(SELECT
SUM(CASE
WHEN Tax=1
THEN CASE WHEN SaleItem=1
THEN CASE WHEN Discount=1 THEN Quantity*SalePrice*(1-#Discount#)*#TaxRate#*1/100 ELSE
Quantity*SalePrice*#TaxRate#*1/100 END
ELSE CASE WHEN Discount=1 THEN Quantity*Price*(1-#Discount#)*#TaxRate#*1/100 ELSE
Quantity*Price*#TaxRate#*1/100 END
END
ELSE 0
END) FROM (Item INNER JOIN CartItems ON Item.ItemID = CartItems.ItemID) WHERE
CartItems.CartID='#CartSumCartID#') as SalesTax,

  • Now see if the total item price is to be used in the calculation of shipping charges by again using nested CASE expressions.
  • If the item is not to be used in the shipping calculation, it is added as 0.

(SELECT
SUM(CASE
WHEN Shipping=1
THEN CASE WHEN SaleItem=1
THEN CASE WHEN Discount=1 THEN Quantity*SalePrice*(1-#Discount#) ELSE Quantity*SalePrice
END
ELSE CASE WHEN Discount=1 THEN Quantity*Price*(1-#Discount#) ELSE Quantity*Price END
END
ELSE 0
END) FROM (Item INNER JOIN CartItems ON Item.ItemID = CartItems.ItemID) WHERE
CartItems.CartID='#CartSumCartID#') as TotalShippingPrice

  • Finish the query.

FROM (Item INNER JOIN CartItems ON Item.ItemID = CartItems.ItemID)
WHERE CartItems.CartID='#CartSumCartID#'
ORDER BY Name
</CFQUERY>

The table returned contains rows of items in the customer's cart with the information about each item and the quantity ordered. In addition, each row of the table includes the item total prior to discount (TotalPrice), the item total after discount (TotalDiscountedPrice), the sales tax to be added to the order (SalesTax), and the item total for items that are to be used in the calculation of shipping charges (TotalShippingPrice).

There is one last task to complete prior to displaying the cart summary to the customer - determining shipping cost. Shipping cost is determined by the value of GetCartItems.TotalShippingPrice. ShipCost is returned by the following query where the value of GetCartItems.TotalShippingPrice falls between or is equal to MinTotal and/or MaxTotal in the Shipping table.

<CFQUERY NAME="GetShippingFee" DATASOURCE="#Application.Datasource#">
SELECT ShipCost
FROM Shipping
WHERE #GetCartItems.TotalShippingPrice# >= MinTotal
and #GetCartItems.TotalShippingPrice# <= MaxTotal
</CFQUERY>

Later the shipping cost and sales tax will be added to the total and passed along to the display template.

Conclusion
The CASE expression allows the programmer to create queries that use logical expressions to determine what value to use and/or what calculation to perform. Used in a subquery, the CASE expression allows for the generation of multiple values from the same data set. The CASE expression is, in many instances, a viable alternative to post query loops and calculations. Application of the CASE expression can help you make the most of SQL in development projects.

More Stories By Hudson Benson

Hudson Benson is the owner of The Cattail Company, a Web and media development company located in northeastern Maryland. His experience includes the development and marketing of Web applications for businesses, universities, and nonprofit organizations. Hudson also serves as North American director of marketing and business development for a global firm headquartered in Sweden.

Comments (1) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
yousokkong 12/18/03 09:35:56 PM EST

Hi how are you

@ThingsExpo Stories
"When we talk about cloud without compromise what we're talking about is that when people think about 'I need the flexibility of the cloud' - it's the ability to create applications and run them in a cloud environment that's far more flexible,” explained Matthew Finnie, CTO of Interoute, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
The Internet giants are fully embracing AI. All the services they offer to their customers are aimed at drawing a map of the world with the data they get. The AIs from these companies are used to build disruptive approaches that cannot be used by established enterprises, which are threatened by these disruptions. However, most leaders underestimate the effect this will have on their businesses. In his session at 21st Cloud Expo, Rene Buest, Director Market Research & Technology Evangelism at Ara...
No hype cycles or predictions of zillions of things here. IoT is big. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, Associate Partner at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He discussed the evaluation of communication standards and IoT messaging protocols, data analytics considerations, edge-to-cloud tec...
New competitors, disruptive technologies, and growing expectations are pushing every business to both adopt and deliver new digital services. This ‘Digital Transformation’ demands rapid delivery and continuous iteration of new competitive services via multiple channels, which in turn demands new service delivery techniques – including DevOps. In this power panel at @DevOpsSummit 20th Cloud Expo, moderated by DevOps Conference Co-Chair Andi Mann, panelists examined how DevOps helps to meet the de...
When growing capacity and power in the data center, the architectural trade-offs between server scale-up vs. scale-out continue to be debated. Both approaches are valid: scale-out adds multiple, smaller servers running in a distributed computing model, while scale-up adds fewer, more powerful servers that are capable of running larger workloads. It’s worth noting that there are additional, unique advantages that scale-up architectures offer. One big advantage is large memory and compute capacity...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
Amazon started as an online bookseller 20 years ago. Since then, it has evolved into a technology juggernaut that has disrupted multiple markets and industries and touches many aspects of our lives. It is a relentless technology and business model innovator driving disruption throughout numerous ecosystems. Amazon’s AWS revenues alone are approaching $16B a year making it one of the largest IT companies in the world. With dominant offerings in Cloud, IoT, eCommerce, Big Data, AI, Digital Assista...
Artificial intelligence, machine learning, neural networks. We’re in the midst of a wave of excitement around AI such as hasn’t been seen for a few decades. But those previous periods of inflated expectations led to troughs of disappointment. Will this time be different? Most likely. Applications of AI such as predictive analytics are already decreasing costs and improving reliability of industrial machinery. Furthermore, the funding and research going into AI now comes from a wide range of com...
Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devic...
We build IoT infrastructure products - when you have to integrate different devices, different systems and cloud you have to build an application to do that but we eliminate the need to build an application. Our products can integrate any device, any system, any cloud regardless of protocol," explained Peter Jung, Chief Product Officer at Pulzze Systems, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA
SYS-CON Events announced today that Ayehu will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on October 31 - November 2, 2017 at the Santa Clara Convention Center in Santa Clara California. Ayehu provides IT Process Automation & Orchestration solutions for IT and Security professionals to identify and resolve critical incidents and enable rapid containment, eradication, and recovery from cyber security breaches. Ayehu provides customers greater control over IT infras...
SYS-CON Events announced today that MobiDev, a client-oriented software development company, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MobiDev is a software company that develops and delivers turn-key mobile apps, websites, web services, and complex software systems for startups and enterprises. Since 2009 it has grown from a small group of passionate engineers and business...
SYS-CON Events announced today that GrapeUp, the leading provider of rapid product development at the speed of business, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market acr...
SYS-CON Events announced today that Enzu will exhibit at SYS-CON's 21st Int\ernational Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to focus on the core of their ...
SYS-CON Events announced today that Cloud Academy named "Bronze Sponsor" of 21st International Cloud Expo which will take place October 31 - November 2, 2017 at the Santa Clara Convention Center in Santa Clara, CA. Cloud Academy is the industry’s most innovative, vendor-neutral cloud technology training platform. Cloud Academy provides continuous learning solutions for individuals and enterprise teams for Amazon Web Services, Microsoft Azure, Google Cloud Platform, and the most popular cloud com...
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
In his session at Cloud Expo, Alan Winters, an entertainment executive/TV producer turned serial entrepreneur, presented a success story of an entrepreneur who has both suffered through and benefited from offshore development across multiple businesses: The smart choice, or how to select the right offshore development partner Warning signs, or how to minimize chances of making the wrong choice Collaboration, or how to establish the most effective work processes Budget control, or how to ma...
SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business - from apparel to energy - is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists looked at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deliver...
In his session at @ThingsExpo, Eric Lachapelle, CEO of the Professional Evaluation and Certification Board (PECB), provided an overview of various initiatives to certify the security of connected devices and future trends in ensuring public trust of IoT. Eric Lachapelle is the Chief Executive Officer of the Professional Evaluation and Certification Board (PECB), an international certification body. His role is to help companies and individuals to achieve professional, accredited and worldwide re...