Skip to main content

23. Math Functions & CASE Logic -- Calculations and Conditionals

Level: Guided beginner

ℹ️ What You'll Learn
  • ROUND, CEILING, FLOOR -- numeric rounding
  • ABS -- absolute value
  • SQRT, POWER -- roots and exponents
  • CASE expression -- conditional logic
  • Simple CASE vs searched CASE
  • CASE for transforming data
  • Multiple CASE conditions
  • Common math/CASE mistakes
  • Best practices

Math functions calculate. CASE expressions conditionally transform. Together they power business logic. This article teaches calculations and conditionals.

Why Math & CASE Matter for Backend Developers​

APIs need conditional business logic:

Frontend asks: "Show student grade (A/B/C/F) based on marks,
                with fees discounted if class avg > 80"
    v
Backend needs:
- Calculate percentage (math)
- Assign grade based on range (CASE)
- Conditional discount (nested CASE)
    v
Math functions and CASE expressions solve in SQL
    v
Return transformed data

CASE expressions replace complex if-else chains.

Math Functions Reference​

ROUND(number, decimals)    -> Round to decimal places
CEILING(number)            -> Smallest integer >= value
FLOOR(number)              -> Largest integer <= value
ABS(number)                -> Absolute value (remove sign)
SQRT(number)               -> Square root
POWER(number, exponent)    -> Power (number^exponent)

Example 1: Round Exam Marks​

Round to nearest integer:

SELECT 
    s.Name AS Student,
    r.MarksObtained AS RawMarks,
    ROUND(r.MarksObtained, 0) AS RoundedMarks,
    ROUND(r.MarksObtained, 1) AS OneDec,
    ROUND(r.MarksObtained, 2) AS TwoDec
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;

Result (rounding):

Student     | RawMarks | RoundedMarks | OneDec | TwoDec
Ravi Kumar  | 92.456   | 92           | 92.5   | 92.46
Priya Sharma| 85.624   | 86           | 85.6   | 85.62

ROUND(value, 0): Integer. ROUND(value, 1): 1 decimal place.

Example 2: Percentage Calculation​

Calculate percentage with proper rounding:

SELECT 
    s.Name AS Student,
    r.MarksObtained,
    e.MaxMarks,
    ROUND(r.MarksObtained * 100.0 / e.MaxMarks, 2) AS Percentage
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId
ORDER BY Percentage DESC;

Result (percentages):

Student     | MarksObtained | MaxMarks | Percentage
Ravi Kumar  | 92            | 100      | 92.00
Priya Sharma| 85            | 100      | 85.00
Arjun Reddy | 78            | 100      | 78.00

Multiply by 100.0 (FLOAT), divide by MaxMarks, ROUND to 2 decimals.

Example 3: CASE Expression (Simple)​

Assign grade based on marks:

SELECT 
    s.Name AS Student,
    r.MarksObtained,
    CASE r.MarksObtained
        WHEN 90 THEN 'A+'
        WHEN 80 THEN 'A'
        WHEN 70 THEN 'B'
        WHEN 60 THEN 'C'
        ELSE 'F'
    END AS Grade
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;

Result (grades):

Student     | MarksObtained | Grade
Ravi Kumar  | 92            | A+
Priya Sharma| 85            | A
Arjun Reddy | 78            | B

Simple CASE: Match exact value. Not useful for ranges.

Example 4: CASE Expression (Searched -- ranges)​

Assign grade by range:

SELECT 
    s.Name AS Student,
    r.MarksObtained,
    CASE
        WHEN r.MarksObtained >= 90 THEN 'A+'
        WHEN r.MarksObtained >= 80 THEN 'A'
        WHEN r.MarksObtained >= 70 THEN 'B'
        WHEN r.MarksObtained >= 60 THEN 'C'
        ELSE 'F'
    END AS Grade
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
ORDER BY r.MarksObtained DESC;

Result (grades by range):

Student     | MarksObtained | Grade
Ravi Kumar  | 92            | A+
Priya Sharma| 85            | A
Arjun Reddy | 78            | B
Kiran Rao   | 65            | C

Searched CASE (WHEN condition): For ranges, multiple conditions.

Example 5: Nested CASE​

Multiple conditions (grade + bonus):

SELECT 
    s.Name AS Student,
    r.MarksObtained,
    CASE
        WHEN r.MarksObtained >= 90 THEN 'A+'
        WHEN r.MarksObtained >= 80 THEN 'A'
        ELSE 'B'
    END AS Grade,
    CASE
        WHEN r.MarksObtained >= 90 THEN 10
        WHEN r.MarksObtained >= 80 THEN 5
        ELSE 0
    END AS BonusPoints
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;

Result (grade + bonus):

Student     | MarksObtained | Grade | BonusPoints
Ravi Kumar  | 92            | A+    | 10
Priya Sharma| 85            | A     | 5
Arjun Reddy | 78            | B     | 0

Multiple CASE expressions per row. Each independent.

Example 6: CASE in WHERE Clause​

Filter by conditional logic:

SELECT 
    s.Name AS Student,
    r.MarksObtained,
    CASE
        WHEN r.MarksObtained >= 75 THEN 'Pass'
        ELSE 'Fail'
    END AS Result
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
WHERE CASE
        WHEN r.MarksObtained >= 75 THEN 'Pass'
        ELSE 'Fail'
    END = 'Pass'
ORDER BY r.MarksObtained DESC;

Result (pass students only):

Student     | MarksObtained | Result
Ravi Kumar  | 92            | Pass
Priya Sharma| 85            | Pass

CASE in WHERE: Filter by conditional result.

Example 7: Fee Discount Logic​

Apply discount based on class average:

SELECT 
    s.Name AS Student,
    f.TotalFees,
    CASE
        WHEN AVG(r.MarksObtained) OVER (PARTITION BY s.ClassName) > 80
        THEN ROUND(f.TotalFees * 0.9, 0)  -- 10% discount
        WHEN AVG(r.MarksObtained) OVER (PARTITION BY s.ClassName) > 70
        THEN ROUND(f.TotalFees * 0.95, 0) -- 5% discount
        ELSE f.TotalFees                   -- No discount
    END AS DiscountedFees
FROM Student s
LEFT JOIN FeeAccount f ON s.StudentId = f.StudentId
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
GROUP BY s.StudentId, s.Name, f.TotalFees, s.ClassName;

Result (discounts):

Student     | TotalFees | DiscountedFees
Ravi Kumar  | 50000     | 45000  (10% discount, class avg > 80)
Priya Sharma| 50000     | 47500  (5% discount, class avg > 70)

CASE with window function: Conditional based on aggregate.

Example 8: Absolute Value (Difference)​

Get absolute difference from average:

SELECT 
    s.Name AS Student,
    r.MarksObtained,
    AVG(r.MarksObtained) OVER () AS OverallAverage,
    ABS(r.MarksObtained - AVG(r.MarksObtained) OVER ()) AS DifferenceFromAverage
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;

Result (deviation):

Student     | MarksObtained | OverallAverage | DifferenceFromAverage
Ravi Kumar  | 92            | 81.5           | 10.5
Priya Sharma| 85            | 81.5           | 3.5
Arjun Reddy | 70            | 81.5           | 11.5

ABS: Always positive distance from average.

Common Math & CASE Mistakes​

Mistake 1: CASE without ELSE

Wrong:

SELECT 
    MarksObtained,
    CASE
        WHEN MarksObtained >= 90 THEN 'A+'
        WHEN MarksObtained >= 80 THEN 'A'
    END AS Grade
FROM ExamResult;
-- Marks < 80: NULL grade

Result: NULL for unhandled cases.

Fix: Add ELSE:

CASE
    WHEN MarksObtained >= 90 THEN 'A+'
    WHEN MarksObtained >= 80 THEN 'A'
    ELSE 'F'
END AS Grade

Mistake 2: Integer division losing decimals

Wrong:

SELECT 
    MarksObtained * 100 / MaxMarks AS Percentage
-- 85 * 100 / 100 = 8500 / 100 = 85 (integer)
-- 92 * 100 / 100 = 9200 / 100 = 92 (integer)
-- No decimals

Result: Integer percentages (no decimal precision).

Fix: Use FLOAT:

SELECT 
    ROUND(MarksObtained * 100.0 / MaxMarks, 2) AS Percentage
-- 100.0 forces float, preserves decimals

Mistake 3: CASE condition order matters

Wrong:

CASE
    WHEN MarksObtained >= 60 THEN 'Pass'
    WHEN MarksObtained >= 90 THEN 'A+'  -- Never reached (already matched >= 60)
END

Result: 95 marks shows "Pass", not "A+" (first match wins).

Fix: Order conditions from specific to general:

CASE
    WHEN MarksObtained >= 90 THEN 'A+'
    WHEN MarksObtained >= 80 THEN 'A'
    WHEN MarksObtained >= 60 THEN 'Pass'
    ELSE 'Fail'
END

Mistake 4: Using CASE with NULL

Wrong:

CASE
    WHEN MarksObtained = NULL THEN 'Absent'  -- Always false (NULL != NULL)
    ELSE 'Present'
END

Result: All present (absent never matched).

Fix: Use IS NULL:

CASE
    WHEN MarksObtained IS NULL THEN 'Absent'
    ELSE 'Present'
END

Mistake 5: Complex CASE logic in WHERE

Wrong:

SELECT * FROM ExamResult
WHERE CASE
        WHEN MarksObtained >= 80 THEN 'Pass'
        ELSE 'Fail'
    END = 'Pass';
-- Works but slow: repeats CASE for every row

Result: Performance hit.

Fix: Use direct condition:

WHERE MarksObtained >= 80;
-- Simpler, faster

Reserve CASE for complex transforms, not simple filters.

Best Practices for Math & CASE​

  1. Use 100.0 for percentage -- Ensures float division
  2. CASE with ELSE -- Handle all cases explicitly
  3. Order CASE from specific to general -- First match wins
  4. IS NULL in CASE, not = NULL -- NULL never equals anything
  5. Avoid CASE in WHERE if possible -- Use direct condition
  6. Test edge cases -- NULL, zero, negative numbers
  7. Round at display -- Not intermediate calculations
🎯 Q1: What's the difference between ROUND, CEILING, FLOOR?

ROUND: Nearest value.

ROUND(92.456, 0)  -- 92
ROUND(92.556, 0)  -- 93

CEILING: Smallest integer >= value.

CEILING(92.1)  -- 93
CEILING(92.0)  -- 92

FLOOR: Largest integer <= value.

FLOOR(92.9)  -- 92
FLOOR(92.0)  -- 92

Use ROUND for normal rounding. CEILING/FLOOR for always up/down.

🎯 Q2: What's the difference between simple and searched CASE?

Simple CASE (exact match):

CASE MarksObtained
    WHEN 90 THEN 'A+'
    WHEN 80 THEN 'A'
    ELSE 'F'
END

Searched CASE (conditions):

CASE
    WHEN MarksObtained >= 90 THEN 'A+'
    WHEN MarksObtained >= 80 THEN 'A'
    ELSE 'F'
END

Simple: Exact values. Searched: Ranges and complex conditions. Searched more common.

🎯 Q3: How do I calculate percentage safely?

Always use FLOAT in division:

SELECT 
    MarksObtained,
    MaxMarks,
    ROUND(MarksObtained * 100.0 / MaxMarks, 2) AS Percentage
FROM ExamResult;

Multiply by 100.0 (not 100) -- forces float. Divide after. ROUND for decimals.

Why: 85 * 100 / 100 (integer) = 85. 85 * 100.0 / 100 (float) = 85.00.

🎯 Q4: Does CASE order matter?

Yes, first match wins:

CASE
    WHEN MarksObtained >= 60 THEN 'Pass'
    WHEN MarksObtained >= 90 THEN 'A+'  -- Never reached for 95
END
-- 95 matches first condition (>= 60), returns 'Pass'

Correct order (specific to general):

CASE
    WHEN MarksObtained >= 90 THEN 'A+'
    WHEN MarksObtained >= 60 THEN 'Pass'
    ELSE 'Fail'
END

Order from most specific to most general.

🎯 Q5: Can I use CASE in JOIN conditions?

Yes, but avoid if possible:

SELECT * FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
WHERE CASE WHEN r.MarksObtained >= 80 THEN 1 ELSE 0 END = 1;
-- Works but slow

Better:

WHERE r.MarksObtained >= 80;

CASE in WHERE: Reserved for complex conditional logic only. Direct condition faster.

🎯 Q6: How do I handle NULL in CASE?

Wrong (never matches NULL):

CASE WHEN Marks = NULL THEN 'Absent'  -- Never true

Correct (use IS NULL):

CASE 
    WHEN Marks IS NULL THEN 'Absent'
    WHEN Marks >= 60 THEN 'Pass'
    ELSE 'Fail'
END

NULL never equals anything (NULL = NULL is false). Use IS NULL / IS NOT NULL.

🤖Use AI to Learn Faster
⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Math functions and CASE expressions in SQL Server. Try these prompts:

  • "Show how to round exam percentage to 2 decimals"
  • "Explain CASE expression: assign grade (A/B/C/F) by marks range"
  • "What's the difference between ROUND, CEILING, FLOOR?"
  • "How do I safely calculate percentage (avoid integer division)?"
  • "Quiz me: ask 5 questions about CASE, ROUND, grade logic"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article​

24. Window Functions -- Advanced Row-Level Analytics ->

nexcoding.in
nexcoding.in  ·  Free .NET Tutorials  ·  Sahasra Technologies  ·  info@nexcoding.in  ·  +91-9951510727