Mathematical Formulas

SQL Most used formulas

This article introduces you to the most commonly used formulas that can solve a wide variety of problems you may encounter when working with data, particularly in SQL and related environments. Whether you're working with text processing, numerical calculations, or date manipulation, these formulas will help you streamline your work and improve your efficiency. This guide is aimed at users who are familiar with basic SQL functions and are looking to optimize their queries.

Prerequisites

Basic knowledge of SQL queries and functions is recommended to fully understand and apply these formulas.

CASE WHEN

This function provides a powerful tool for conditional data analysis and transformation. It evaluates conditions and returns a result when the first condition is met, enabling you to categorize, filter, or modify data based on a set of rules or criteria.

How to use it:

CASE 
	WHEN condition1 THEN result1 
	WHEN condition2 THEN result2 
	ELSE result3 
END

Issue: You need to categorize products based on price ranges, assign different labels to data entries based on specific conditions, or apply different transformations to data based on predefined rules or criteria.

Example:

CASE 
	WHEN price < 50 THEN "Budget" 
	WHEN price BETWEEN 50 AND 200 THEN "Mid-range" 
	ELSE "Premium"
END

SAFE_CAST or CAST

This function provides a safe and reliable way to convert data between different types. SAFE_CAST returns NULL if the conversion fails, while CAST will throw an error, allowing you to handle potential conversion issues gracefully or enforce strict data type management.

How to use it: SAFE_CAST(value AS TYPE)

Issue: You need to convert textual data to numerical format for calculations, handle potential data type mismatches without causing errors, or ensure that data is correctly interpreted for analysis or reporting.

Example:SAFE_CAST(price AS NUMERIC)

SMART_CUT

This function addresses the need to present concise and readable text summaries. It limits the number of characters in a string without cutting words, ensuring that previews or summaries maintain clarity and grammatical correctness.

How to use it: SMART_CUT(value, length)

Issue: You need to create short summaries of product descriptions for display on a website, truncate customer reviews while preserving readability, or generate previews of textual data for improved user experience.

Example: SMART_CUT(description, 100)

ROUND

This function addresses the need for precise numerical representation and formatting. It rounds a number to a specific number of decimal places, ensuring that financial data, calculations, or any numerical output adheres to the desired level of accuracy.

How to use it:

Round to the nearest integer: ROUND(value)
Round to two decimal places:ROUND(value, 2)

Issue: You need to display financial data with a specific number of decimal places, round calculated values to the nearest integer for reporting, or ensure that numerical data is presented with the appropriate level of precision.

Example:ROUND(123.456, 2) -- Result: 123.46

IN

This function simplifies the process of checking multiple conditions, improving the efficiency and readability of your queries. It checks if a value is present within a list of values, allowing you to categorize or filter data based on a set of predefined criteria.

How to use it: value IN (value1, value2, value3)

Issue: You need to filter products that belong to specific categories, identify customer reviews that contain certain keywords, or analyze data based on predefined criteria or categories.

Example: category IN ('Electronics', 'Furniture', 'Clothing')

VALUE_RANGE

This function checks if a numerical value falls within a specified minimum and maximum, returning TRUE if it does and FALSE otherwise. It's useful for data validation on numerical columns.

Watch out: This formula works only for columns that are NUMBER

How to use it: VALUE_RANGE(column, minimum_value, maximum_value)

Issue: You need to validate that the values in a numerical column, such as product prices, are within an acceptable or expected range for quality control or specific business rules.

Example: VALUE_RANGE(product_price, 5.00, 50.00)

Quick Answers to Common Issues

What if the IN formula doesn't return the expected results?

Double-check that the values in the list are correctly formatted and match the data type of the column being checked.

Where can I find more information about these mathematical functions and how to use them effectively?

You can access the formula library directly in the editor, where you'll find the majority of these formulas ready to use, along with detailed documentation and examples.

For further assistance with SmartFeeds, consider reviewing these articles:

For additional help, feel free to reach out via our Contact Us page.