How to Use the CASE Statement in SQL for Conditional Logic

The CASE statement in SQL allows you to implement conditional logic within queries, making it a powerful tool for handling complex data transformations and classifications.

Basic Syntax

The CASE statement works like an IF-ELSE structure, evaluating conditions and returning corresponding values:

SELECT column_name,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE default_result
       END AS alias_name
FROM table_name;

Example: Categorizing Data

Assume we have an Employees table with an Age column, and we want to categorize employees based on their age groups:

SELECT Name, Age,
       CASE
           WHEN Age < 25 THEN 'Young'
           WHEN Age BETWEEN 25 AND 40 THEN 'Mid-Age'
           ELSE 'Senior'
       END AS AgeCategory
FROM Employees;

Output Example:

Name Age AgeCategory
Alice 22 Young
Bob 30 Mid-Age
Charlie 50 Senior

Using CASE in Aggregations

CASE is often used in aggregate functions to perform conditional counting or summing:

SELECT
    SUM(CASE WHEN Age < 25 THEN 1 ELSE 0 END) AS YoungCount,
    SUM(CASE WHEN Age BETWEEN 25 AND 40 THEN 1 ELSE 0 END) AS MidAgeCount,
    SUM(CASE WHEN Age > 40 THEN 1 ELSE 0 END) AS SeniorCount
FROM Employees;

Using CASE in ORDER BY

You can use CASE to customize sorting order dynamically:

SELECT Name, Age
FROM Employees
ORDER BY
    CASE
        WHEN Age < 25 THEN 1
        WHEN Age BETWEEN 25 AND 40 THEN 2
        ELSE 3
    END;

Conclusion

The CASE statement is a versatile tool in SQL for implementing conditional logic in SELECT, WHERE, ORDER BY, and aggregate functions. It enhances query flexibility, making data classification and transformation more efficient.

0
33

Related

In C#, you can format an integer with commas (thousands separator) using ToString with a format specifier.

int number = 1234567;
string formattedNumber = number.ToString("N0"); // "1,234,567"
Console.WriteLine(formattedNumber);

Explanation:

"N0": The "N" format specifier stands for Number, and "0" means no decimal places. The output depends on the culture settings, so in regions where , is the decimal separator, you might get 1.234.567.

Alternative:

You can also specify culture explicitly if you need a specific format:

using System.Globalization;

int number = 1234567;
string formattedNumber = number.ToString("N0", CultureInfo.InvariantCulture);
Console.WriteLine(formattedNumber); // "1,234,567"
3
380

When working with financial data in C#, proper currency formatting is essential for clear and professional presentation. The .NET framework provides several convenient methods to format numeric values as currency, with the most common being the ToString() method with the "C" format specifier.

For example, decimal amount = 1234.56m; string formatted = amount.ToString("C"); will display "$1,234.56" in US culture.

For more control over the formatting, you can specify a culture explicitly using CultureInfo - amount.ToString("C", new CultureInfo("fr-FR")) would display "1 234,56 €".

This allows your application to handle different currency symbols, decimal separators, and grouping conventions appropriately.

If you need to handle multiple currencies or require more specialized formatting, you can also use the String.Format() method or string interpolation with custom format strings.

For instance, String.Format("{0:C}", amount) or $"{amount:C}" achieves the same result as ToString("C"). Additionally, you can control the number of decimal places using format strings like "C2" for two decimal places.

Remember that when dealing with financial calculations, it's best practice to use the decimal type rather than float or double to avoid rounding errors that could impact currency calculations.

Example

decimal price = 1234.56m;
// Basic currency formatting
Console.WriteLine(price.ToString("C")); // Output: $1,234.56

// Currency formatting with specific culture
Console.WriteLine(price.ToString("C", new CultureInfo("de-DE"))); // Output: 1.234,56 €

// Currency formatting with string interpolation
Console.WriteLine($"{price:C}"); // Output: $1,234.56

// Controlling decimal places
Console.WriteLine(price.ToString("C3")); // Output: $1,234.560
0
617

In C#, readonly, const, and static are keywords used to define variables with different behaviors in terms of mutability, memory allocation, and scope.

Understanding their differences is crucial for writing efficient and maintainable code. In this article we'll take a look at each and see how they are used.

1. const (Constant Values)

A const variable is a compile-time constant, meaning its value must be assigned at declaration and cannot be changed later.

Key Characteristics:

  • Must be assigned at declaration.
  • Stored in the assembly metadata (not allocated memory at runtime).
  • Can only be assigned primitive types, string, or enum values.
  • Cannot be modified after compilation.

Example:

public class MathConstants
{
    public const double Pi = 3.14159;
}

// Usage:
Console.WriteLine(MathConstants.Pi); // Output: 3.14159

Limitations:

  • Since const values are replaced at compile-time, updating a const in a library requires recompiling all dependent projects.
  • Cannot use non-primitive types (e.g., objects, lists).

2. readonly (Runtime Immutable Fields)

A readonly field allows initialization either at declaration or in the constructor but cannot be modified afterward.

Key Characteristics:

  • Can be assigned at declaration or inside a constructor.
  • Its value can change during runtime (but only in the constructor).
  • Works with all data types, including objects.
  • More flexible than const since values are resolved at runtime.

Example:

public class Circle
{
    public readonly double Radius;
    public readonly double Pi = 3.14159;

    public Circle(double radius)
    {
        Radius = radius; // Allowed because it's inside the constructor.
    }
}

// Usage:
Circle c = new Circle(5);
Console.WriteLine(c.Radius); // Output: 5

Best for: Values that should remain constant per instance but need to be assigned dynamically at runtime.


3. static (Shared Across All Instances)

A static variable belongs to the type itself rather than to any instance of the class.

Key Characteristics:

  • Shared across all instances of a class.
  • Cannot be used with instance constructors.
  • Initialized once and persists for the application’s lifetime.
  • Can be combined with readonly or const.

Example:

public class GlobalConfig
{
    public static string ApplicationName = "MyApp";
    public static readonly DateTime StartTime = DateTime.Now;
}

// Usage:
Console.WriteLine(GlobalConfig.ApplicationName); // Output: MyApp

Best for: Global state, caching, configuration values, and utility methods.


Key Differences Summary

Feature const readonly static
Mutability Immutable Immutable (after construction) Mutable
When Set Compile-time Runtime (constructor) Runtime
Memory Usage Stored in metadata Instance-based Type-based (shared)
Can Use Objects? ❌ No ✅ Yes ✅ Yes
Can Change After Initialization? ❌ No ❌ No (after constructor) ✅ Yes

Choosing the Right One:

  • Use const for fixed, compile-time values that will never change.
  • Use readonly for immutable values that need runtime initialization.
  • Use static for class-level data shared across all instances.

Understanding these differences helps you write cleaner, more efficient C# code. Happy coding! 🚀

0
77