sreekarun/SQL_cast.md

CAST as Data Type in SQLite

SETUP — Table Used

SELECT * FROM netflix_titles LIMIT 5;
PRAGMA table_info(netflix_titles);

Assume netflix_titles has: title_id, title, genre, rating, imdb_score, release_year, duration_mins, is_original, is_canceled, country, language

With some columns stored as wrong types: imdb_score stored as TEXT ('8.5') release_year stored as TEXT ('2019') duration_mins stored as TEXT ('120')

PART 1 — BASICS

What CAST Does

SQLite stores data loosely — a column declared as INTEGER
can hold text, a TEXT column can hold numbers.

CAST forces a value to be treated as a specific type
for the duration of that query.

It does NOT change the data stored in the table.
It only affects how the value is computed or displayed.

Syntax

CAST(value AS type)
CAST(column AS type)
CAST(expression AS type)

-- SQLite type names accepted:
--   INTEGER   whole numbers
--   REAL      decimal numbers (float)
--   TEXT      string
--   NUMERIC   tries INTEGER first, then REAL
--   BLOB      raw binary data (rare)

1. Basic CAST Examples

-- Text score to decimal number
SELECT CAST('8.5' AS REAL) AS score;           -- → 8.5

-- Text year to integer
SELECT CAST('2019' AS INTEGER) AS year;        -- → 2019

-- Number to text
SELECT CAST(8.5 AS TEXT) AS score_text;        -- → '8.5'

-- Decimal to integer (truncates, does not round)
SELECT CAST(8.9 AS INTEGER) AS truncated;      -- → 8
SELECT CAST(8.1 AS INTEGER) AS truncated;      -- → 8

2. CAST a Column

-- imdb_score stored as TEXT — cast to REAL for math
SELECT
    title,
    imdb_score,
    CAST(imdb_score AS REAL) AS score_number
FROM netflix_titles;

-- release_year stored as TEXT — cast to INTEGER for comparisons
SELECT
    title,
    CAST(release_year AS INTEGER) AS year_int
FROM netflix_titles
WHERE CAST(release_year AS INTEGER) > 2018;

-- duration_mins stored as TEXT — cast for arithmetic
SELECT
    title,
    CAST(duration_mins AS INTEGER) AS mins,
    CAST(duration_mins AS INTEGER) / 60.0 AS hours
FROM netflix_titles;

PART 2 — WHY CAST MATTERS

3. The Integer Division Problem

-- ❌ Wrong — both sides are INTEGER → result is integer (truncated)
SELECT 7 / 2;                    -- → 3  (not 3.5)
SELECT duration_mins / 60;       -- → 3  (loses decimal)

-- ✅ Fix with CAST
SELECT CAST(7 AS REAL) / 2;      -- → 3.5
SELECT CAST(duration_mins AS REAL) / 60 AS hours
FROM netflix_titles;

-- ✅ Alternative — multiply by 1.0
SELECT duration_mins * 1.0 / 60 AS hours
FROM netflix_titles;

4. Text Stored as Number — sorting breaks without CAST

-- ❌ Wrong — TEXT sort puts '10' before '9' (alphabetical)
SELECT release_year
FROM netflix_titles
ORDER BY release_year;           -- '10', '2', '2019', '9'  ← wrong

-- ✅ Correct — CAST to INTEGER first
SELECT release_year
FROM netflix_titles
ORDER BY CAST(release_year AS INTEGER);   -- 2, 9, 10, 2019 ← correct

5. Text Stored as Number — aggregates give wrong results

-- ❌ Wrong — MIN/MAX on text uses alphabetical comparison
SELECT MIN(release_year), MAX(release_year)
FROM netflix_titles;            -- '2019' could come before '2022' but '9' > '2019'

-- ✅ Correct — CAST first
SELECT
    MIN(CAST(release_year AS INTEGER)) AS earliest,
    MAX(CAST(release_year AS INTEGER)) AS latest
FROM netflix_titles;

-- ❌ Wrong — SUM/AVG on text columns return NULL or 0
SELECT AVG(imdb_score)
FROM netflix_titles;            -- may return 0.0 if stored as TEXT

-- ✅ Correct
SELECT ROUND(AVG(CAST(imdb_score AS REAL)), 2) AS avg_score
FROM netflix_titles;

6. Percentage Calculation — force float division

-- ❌ Wrong — integer division returns 0
SELECT
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) /
    COUNT(*) AS pct_original
FROM netflix_titles;            -- → 0

-- ✅ Fix with CAST
SELECT
    CAST(SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) AS REAL)
    / COUNT(*) * 100 AS pct_original
FROM netflix_titles;

-- ✅ Simpler fix — multiply by 100.0
SELECT
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0
    / COUNT(*) AS pct_original
FROM netflix_titles;

PART 3 — CAST WITH COMMON OPERATIONS

7. CAST in WHERE — filter text-stored numbers correctly

-- Filter by score stored as TEXT
SELECT title, imdb_score
FROM netflix_titles
WHERE CAST(imdb_score AS REAL) >= 8.0;

-- Filter by year stored as TEXT
SELECT title, release_year
FROM netflix_titles
WHERE CAST(release_year AS INTEGER) BETWEEN 2018 AND 2022;

-- Filter by duration stored as TEXT
SELECT title, duration_mins
FROM netflix_titles
WHERE CAST(duration_mins AS INTEGER) > 90;

8. CAST in SELECT — transform for display or calculation

-- Show score as integer (drop decimals)
SELECT
    title,
    imdb_score                          AS raw_score,
    CAST(imdb_score AS INTEGER)         AS score_int,
    CAST(imdb_score AS REAL)            AS score_real
FROM netflix_titles;

-- Show duration in hours as a decimal
SELECT
    title,
    CAST(duration_mins AS REAL) / 60          AS hours,
    ROUND(CAST(duration_mins AS REAL) / 60, 1) AS hours_rounded
FROM netflix_titles;

-- Combine CAST with ROUND
SELECT
    title,
    ROUND(CAST(imdb_score AS REAL), 1) AS score_1dp
FROM netflix_titles;

9. CAST in GROUP BY and ORDER BY

-- Group by year correctly when stored as TEXT
SELECT
    CAST(release_year AS INTEGER) AS year,
    COUNT(*)                       AS title_count,
    ROUND(AVG(CAST(imdb_score AS REAL)), 2) AS avg_score
FROM netflix_titles
GROUP BY CAST(release_year AS INTEGER)
ORDER BY CAST(release_year AS INTEGER) DESC;

-- Sort durations numerically not alphabetically
SELECT title, duration_mins
FROM netflix_titles
ORDER BY CAST(duration_mins AS INTEGER) DESC;

10. CAST with CASE

-- Label based on cast score
SELECT
    title,
    imdb_score,
    CASE
        WHEN CAST(imdb_score AS REAL) >= 8.5 THEN 'Must Watch'
        WHEN CAST(imdb_score AS REAL) >= 7.0 THEN 'Worth Watching'
        WHEN CAST(imdb_score AS REAL) >= 5.5 THEN 'Average'
        ELSE                                      'Skip It'
    END AS recommendation
FROM netflix_titles;

11. CAST to TEXT — build formatted strings

-- Build a readable label combining text and numbers
SELECT
    title,
    CAST(release_year AS TEXT) || ' - ' || title AS display_label
FROM netflix_titles;

-- Concatenate year and genre as a tag
SELECT
    title,
    genre || ' (' || CAST(release_year AS TEXT) || ')' AS genre_tag
FROM netflix_titles;

In SQLite || is the string concatenation operator. Numbers must be CAST to TEXT before concatenating.

PART 4 — CAST WITH AGGREGATION

12. CAST inside aggregation functions

-- Correct average when score is stored as TEXT
SELECT
    genre,
    COUNT(*)                                    AS total,
    ROUND(AVG(CAST(imdb_score AS REAL)), 2)     AS avg_score,
    MIN(CAST(imdb_score AS REAL))               AS min_score,
    MAX(CAST(imdb_score AS REAL))               AS max_score,
    SUM(CAST(duration_mins AS INTEGER))         AS total_minutes
FROM netflix_titles
GROUP BY genre
ORDER BY avg_score DESC;

13. CAST for percentage in aggregation

-- Percentage of originals per genre with CAST for safe division
SELECT
    genre,
    COUNT(*) AS total,
    ROUND(
        CAST(SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) AS REAL)
        / COUNT(*) * 100,
        1
    ) AS pct_original
FROM netflix_titles
GROUP BY genre
ORDER BY pct_original DESC;

PART 5 — CAST BEHAVIOR REFERENCE

14. What happens at the boundaries

-- CAST bad text to number → returns 0 or partial parse
SELECT CAST('abc'  AS INTEGER);   -- → 0
SELECT CAST('8abc' AS REAL);      -- → 8.0  (reads until non-numeric)
SELECT CAST(''     AS INTEGER);   -- → 0
SELECT CAST(NULL   AS INTEGER);   -- → NULL  (NULL stays NULL)

-- CAST decimal to INTEGER → truncates toward zero (no rounding)
SELECT CAST(8.9  AS INTEGER);     -- → 8
SELECT CAST(-8.9 AS INTEGER);     -- → -8   (toward zero, not -9)

-- CAST large number to TEXT
SELECT CAST(1234567 AS TEXT);     -- → '1234567'

15. CAST vs multiply by 1.0 — two ways to force float

-- These produce the same result
SELECT CAST(duration_mins AS REAL) / 60 FROM netflix_titles;
SELECT duration_mins * 1.0        / 60 FROM netflix_titles;

-- CAST is more explicit and self-documenting
-- * 1.0 is shorter for quick inline use

🧠 Quick Reference

Goal	Pattern
Text → number for math	`CAST(col AS REAL)` or `CAST(col AS INTEGER)`
Force float division	`CAST(numerator AS REAL) / denominator`
Number → text for concat	`CAST(col AS TEXT)`
Fix alphabetical sort of numbers	`ORDER BY CAST(col AS INTEGER)`
Fix AVG / MIN / MAX on text column	`AVG(CAST(col AS REAL))`
Fix percentage calculation	`SUM(...) * 100.0 / COUNT(*)`
Truncate decimal to integer	`CAST(col AS INTEGER)`
Preserve decimal in division	`col * 1.0 / divisor`
CAST in WHERE filter	`WHERE CAST(col AS REAL) > 7.0`
CAST in GROUP BY	`GROUP BY CAST(col AS INTEGER)`

🧠 Mental Model

CAST is a temporary lens — it changes how SQLite
reads the value in that query, not what is stored.

When to reach for CAST:
  ❶ Math on a column stored as TEXT
        CAST(imdb_score AS REAL) for AVG, SUM, MIN, MAX
  ❷ Integer division producing 0 instead of a decimal
        CAST(numerator AS REAL) / denominator
        or multiply by 100.0 for percentages
  ❸ Sorting numbers stored as TEXT
        ORDER BY CAST(release_year AS INTEGER)
  ❹ Concatenating a number into a string
        CAST(release_year AS TEXT) || ' release'
  ❺ Filtering text-stored numbers correctly
        WHERE CAST(duration_mins AS INTEGER) > 90

Truncation vs Rounding:
  CAST(8.9 AS INTEGER) → 8  ← truncates, does NOT round
  ROUND(8.9)           → 9  ← rounds
  ROUND(CAST(...))     → cast first, then round

NULL behavior:
  CAST(NULL AS INTEGER) → NULL
  NULL stays NULL regardless of CAST type