SQL Number operations -- COUNT, SUM, ROUND

Number Operations — COUNT, SUM, ROUND 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_documentary, is_comedy, is_canceled, country, language

PART 1 — COUNT

Syntax

COUNT(*)          -- count ALL rows including NULLs
COUNT(column)     -- count NON-NULL values in column
COUNT(DISTINCT column)  -- count unique non-null values

1. Count All Rows

-- How many titles are in the table?
SELECT COUNT(*) AS total_titles
FROM netflix_titles;

2. Count Non-NULL Values in a Column

-- How many titles have an IMDb score recorded?
SELECT COUNT(imdb_score) AS scored_titles
FROM netflix_titles;

-- Compare to total — difference reveals how many are missing scores
SELECT
    COUNT(*)           AS total_titles,
    COUNT(imdb_score)  AS titles_with_score,
    COUNT(*) - COUNT(imdb_score) AS missing_score
FROM netflix_titles;

3. COUNT vs COUNT(*) — the NULL difference

-- country has some NULLs
SELECT
    COUNT(*)        AS all_rows,         -- includes NULL country rows
    COUNT(country)  AS non_null_country  -- excludes NULL country rows
FROM netflix_titles;

Rule: COUNT(*) never skips rows. COUNT(col) skips NULLs. Use COUNT(*) for total rows. Use COUNT(col) to check data completeness.

4. COUNT DISTINCT — unique values only

-- How many unique genres exist?
SELECT COUNT(DISTINCT genre) AS unique_genres
FROM netflix_titles;

-- How many unique countries produce content?
SELECT COUNT(DISTINCT country) AS producing_countries
FROM netflix_titles;

-- How many unique release years?
SELECT COUNT(DISTINCT release_year) AS years_covered
FROM netflix_titles;

5. COUNT per Group — with GROUP BY

-- How many titles per genre?
SELECT
    genre,
    COUNT(*) AS title_count
FROM netflix_titles
GROUP BY genre
ORDER BY title_count DESC;

Result:

genre	title_count
Drama	73
Comedy	47
Documentary	37
Thriller	29

6. COUNT with CASE — conditional count

-- Count originals vs licensed within each genre
SELECT
    genre,
    COUNT(*)                                                    AS total,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END)         AS original_count,
    SUM(CASE WHEN is_original = 'N' THEN 1 ELSE 0 END)         AS licensed_count
FROM netflix_titles
GROUP BY genre;

Use SUM(CASE ...) not COUNT(CASE ...) for conditional counts. COUNT counts any non-NULL — including 0 — so CASE WHEN ... THEN 1 ELSE 0 would count everything. SUM adds actual values giving the correct count.

-- ❌ Wrong — counts both 1 and 0
COUNT(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END)

-- ✅ Correct — sums only the 1s
SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END)

-- ✅ Also correct — NULL not counted by COUNT
COUNT(CASE WHEN is_original = 'Y' THEN 1 END)

7. HAVING with COUNT — filter groups by count

-- Only show genres with more than 20 titles
SELECT
    genre,
    COUNT(*) AS title_count
FROM netflix_titles
GROUP BY genre
HAVING COUNT(*) > 20
ORDER BY title_count DESC;

-- Genres with exactly 1 title
SELECT genre, COUNT(*) AS title_count
FROM netflix_titles
GROUP BY genre
HAVING COUNT(*) = 1;

PART 2 — SUM

Syntax

SUM(column)                          -- sum all non-NULL values
SUM(CASE WHEN condition THEN val END) -- conditional sum

8. Basic SUM

-- Total watch time across all titles
SELECT SUM(duration_mins) AS total_minutes
FROM netflix_titles;

-- Convert to hours
SELECT SUM(duration_mins) / 60 AS total_hours
FROM netflix_titles;

-- Convert to days
SELECT SUM(duration_mins) / 60 / 24 AS total_days
FROM netflix_titles;

9. SUM per Group

-- Total duration per genre
SELECT
    genre,
    SUM(duration_mins)        AS total_minutes,
    SUM(duration_mins) / 60   AS total_hours
FROM netflix_titles
GROUP BY genre
ORDER BY total_minutes DESC;

10. SUM with CASE — conditional totals

-- Total minutes split by original vs licensed
SELECT
    SUM(CASE WHEN is_original = 'Y' THEN duration_mins ELSE 0 END) AS original_minutes,
    SUM(CASE WHEN is_original = 'N' THEN duration_mins ELSE 0 END) AS licensed_minutes,
    SUM(duration_mins)                                              AS total_minutes
FROM netflix_titles;

11. SUM for Binary Percentage — the exam pattern

-- What % of titles are Netflix Originals?
SELECT
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END)                AS original_count,
    COUNT(*)                                                           AS total,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0
        / COUNT(*)                                                     AS pct_original
FROM netflix_titles;

12. SUM vs COUNT — know the difference

SELECT
    COUNT(*)                                            AS row_count,
    COUNT(duration_mins)                                AS non_null_duration_count,
    SUM(duration_mins)                                  AS total_duration,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) AS original_count
FROM netflix_titles;

COUNT(*)         → how many rows exist
COUNT(col)       → how many rows have a value (non-NULL)
SUM(col)         → what is the total value
SUM(CASE ...)    → how many rows meet a condition (conditional count)

13. Running Total with SUM — window function

-- Cumulative title count by release year
SELECT
    release_year,
    COUNT(*)                                              AS titles_that_year,
    SUM(COUNT(*)) OVER (ORDER BY release_year)            AS running_total
FROM netflix_titles
GROUP BY release_year
ORDER BY release_year;

PART 3 — ROUND

Syntax

ROUND(value, decimal_places)
ROUND(value)          -- rounds to nearest integer (0 decimal places)

14. Basic ROUND

-- Round IMDb scores to 1 decimal place
SELECT
    title,
    imdb_score,
    ROUND(imdb_score, 1)  AS score_1dp,
    ROUND(imdb_score, 0)  AS score_rounded,
    ROUND(imdb_score)     AS score_int
FROM netflix_titles;

Result:

title	imdb_score	score_1dp	score_rounded	score_int
Stranger Things	8.743	8.7	9.0	9.0
Squid Game	8.046	8.0	8.0	8.0
Emily in Paris	6.321	6.3	6.0	6.0

15. ROUND on Calculated Values

-- Round average score per genre to 2 decimal places
SELECT
    genre,
    COUNT(*)                      AS total,
    ROUND(AVG(imdb_score), 2)     AS avg_score,
    ROUND(AVG(duration_mins), 0)  AS avg_duration_mins
FROM netflix_titles
GROUP BY genre
ORDER BY avg_score DESC;

16. ROUND on Percentages

-- Percentage of originals per genre, rounded to 1 decimal
SELECT
    genre,
    COUNT(*) AS total,
    ROUND(
        SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        1
    ) AS pct_original
FROM netflix_titles
GROUP BY genre
ORDER BY pct_original DESC;

17. ROUND to Nearest 10 / 100 — negative decimal places

-- Round duration to nearest 10 minutes
SELECT
    title,
    duration_mins,
    ROUND(duration_mins, -1)   AS rounded_to_10,
    ROUND(duration_mins, -2)   AS rounded_to_100
FROM netflix_titles;

Result:

title	duration_mins	rounded_to_10	rounded_to_100
The Irishman	209	210	200
Bird Box	124	120	100
Roma	135	140	100

18. ROUND with Integer Division Warning

-- ❌ Wrong — integer division gives 0
SELECT 3 / 4;                         -- → 0

-- ❌ Still wrong
SELECT COUNT(*) / 4;                  -- → integer result

-- ✅ Force float division with 1.0 or 100.0
SELECT 3 * 1.0 / 4;                   -- → 0.75
SELECT COUNT(*) * 1.0 / 4;            -- → correct decimal

-- ✅ ROUND the result
SELECT ROUND(COUNT(*) * 100.0 / 4, 1);

PART 4 — COMBINING ALL THREE

19. Full Summary Query

-- Complete genre breakdown with count, sum, and rounded metrics
SELECT
    genre,
    COUNT(*)                                                           AS total_titles,
    COUNT(DISTINCT country)                                            AS producing_countries,
    SUM(duration_mins)                                                 AS total_minutes,
    ROUND(AVG(duration_mins), 0)                                       AS avg_duration,
    ROUND(AVG(imdb_score), 2)                                          AS avg_score,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END)                AS original_count,
    ROUND(
        SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0
            / COUNT(*), 1
    )                                                                  AS pct_original,
    ROUND(
        SUM(CASE WHEN is_canceled = 'Y' THEN 1 ELSE 0 END) * 100.0
            / COUNT(*), 1
    )                                                                  AS pct_canceled
FROM netflix_titles
GROUP BY genre
HAVING COUNT(*) > 10
ORDER BY total_titles DESC;

20. Binary Indicators Summary — UNION ALL pattern

-- One row per binary flag with count, total, and rounded percentage
SELECT
    'Is Original'                                                       AS indicator,
    SUM(CASE WHEN is_original    = 'Y' THEN 1 ELSE 0 END)              AS flag_count,
    COUNT(*)                                                            AS total,
    ROUND(SUM(CASE WHEN is_original    = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS percentage
FROM netflix_titles
UNION ALL
SELECT
    'Is Documentary',
    SUM(CASE WHEN is_documentary = 'Y' THEN 1 ELSE 0 END),
    COUNT(*),
    ROUND(SUM(CASE WHEN is_documentary = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM netflix_titles
UNION ALL
SELECT
    'Is Comedy',
    SUM(CASE WHEN is_comedy      = 'Y' THEN 1 ELSE 0 END),
    COUNT(*),
    ROUND(SUM(CASE WHEN is_comedy      = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM netflix_titles
UNION ALL
SELECT
    'Is Canceled',
    SUM(CASE WHEN is_canceled    = 'Y' THEN 1 ELSE 0 END),
    COUNT(*),
    ROUND(SUM(CASE WHEN is_canceled    = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM netflix_titles;

🧠 Quick Reference

Goal	Function	Example
Count all rows	`COUNT(*)`	`SELECT COUNT(*) FROM t`
Count non-null values	`COUNT(col)`	`COUNT(imdb_score)`
Count unique values	`COUNT(DISTINCT col)`	`COUNT(DISTINCT genre)`
Conditional count	`SUM(CASE WHEN ... THEN 1 ELSE 0 END)`	count of originals
Total of a column	`SUM(col)`	`SUM(duration_mins)`
Conditional total	`SUM(CASE WHEN ... THEN col ELSE 0 END)`	sum for originals only
Percentage	`SUM(...) * 100.0 / COUNT(*)`	% originals
Round to N decimals	`ROUND(val, N)`	`ROUND(AVG(score), 2)`
Round to integer	`ROUND(val)` or `ROUND(val, 0)`	`ROUND(6.7)` → 7
Round to nearest 10	`ROUND(val, -1)`	`ROUND(209, -1)` → 210

🧠 Mental Model

COUNT  → answers "HOW MANY?"
  COUNT(*)         → all rows, nulls included
  COUNT(col)       → only filled rows, nulls skipped
  COUNT(DISTINCT)  → unique values only

SUM    → answers "HOW MUCH / HOW MANY total?"
  SUM(col)         → add up numeric values
  SUM(CASE ...)    → conditional count or conditional total

ROUND  → answers "WHAT IS IT, CLEANED UP?"
  ROUND(val, 2)    → 2 decimal places
  ROUND(val, 0)    → nearest whole number
  ROUND(val, -1)   → nearest 10

Integer Division Trap:
  3 / 4       = 0      ← SQLite does integer math
  3 * 1.0 / 4 = 0.75   ← force float with 1.0 or 100.0
  Always multiply by 100.0 before dividing for percentages

sreekarun/SQL_NumberOps.md

Select an option

No results found

Select an option

No results found

Number Operations — COUNT, SUM, ROUND in SQLite

SETUP — Table Used

PART 1 — COUNT

Syntax

1. Count All Rows

2. Count Non-NULL Values in a Column

3. COUNT vs COUNT(*) — the NULL difference

4. COUNT DISTINCT — unique values only

5. COUNT per Group — with GROUP BY

6. COUNT with CASE — conditional count

7. HAVING with COUNT — filter groups by count

PART 2 — SUM

Syntax

8. Basic SUM

9. SUM per Group

10. SUM with CASE — conditional totals

11. SUM for Binary Percentage — the exam pattern

12. SUM vs COUNT — know the difference

13. Running Total with SUM — window function

PART 3 — ROUND

Syntax

14. Basic ROUND

15. ROUND on Calculated Values

16. ROUND on Percentages

17. ROUND to Nearest 10 / 100 — negative decimal places

18. ROUND with Integer Division Warning

PART 4 — COMBINING ALL THREE

19. Full Summary Query

20. Binary Indicators Summary — UNION ALL pattern

🧠 Quick Reference

🧠 Mental Model