SQLite COUNT SUM AVG

Aggregate functions perform calculations across multiple rows and return a single result. COUNT counts rows, SUM adds numeric values, and AVG calculates the mean. These three functions power most reporting and analytics in SQLite applications.

Sample Table

sales
──────────────────────────────────────────────────────────
id │ rep     │ region  │ product   │ amount │ quarter
───┼─────────┼─────────┼───────────┼────────┼────────
1  │ Alice   │ North   │ Laptop    │ 1200   │ Q1
2  │ Bob     │ South   │ Phone     │ 800    │ Q1
3  │ Alice   │ North   │ Tablet    │ 500    │ Q2
4  │ Carol   │ East    │ Laptop    │ 1100   │ Q1
5  │ Bob     │ South   │ Laptop    │ 1300   │ Q2
6  │ Carol   │ East    │ Phone     │ 750    │ Q2
7  │ Alice   │ West    │ Phone     │ 900    │ Q2
8  │ Bob     │ South   │ Tablet    │ NULL   │ Q3

COUNT — How Many Rows

-- Count all rows (including NULLs)
SELECT COUNT(*) AS total_sales FROM sales;
total_sales: 8

-- Count only rows where amount is not NULL
SELECT COUNT(amount) AS paid_sales FROM sales;
paid_sales: 7   ← Row 8 (NULL amount) excluded

-- Count distinct values in a column
SELECT COUNT(DISTINCT rep) AS unique_reps FROM sales;
unique_reps: 3

SUM — Total of a Column

-- Total revenue across all sales
SELECT SUM(amount) AS total_revenue FROM sales;
total_revenue: 6550   ← NULL in row 8 is skipped

-- Total revenue for Alice only
SELECT SUM(amount) AS alice_total
FROM sales
WHERE rep = 'Alice';
alice_total: 2600   (1200 + 500 + 900)

AVG — Average Value

-- Average sale amount
SELECT AVG(amount) AS avg_sale FROM sales;
avg_sale: 935.71   (6550 / 7 non-NULL rows)

-- Average for Q1 only
SELECT AVG(amount) AS avg_q1
FROM sales
WHERE quarter = 'Q1';
avg_q1: 1033.33   (1200 + 800 + 1100) / 3

Combining Multiple Aggregates

SELECT
  COUNT(*)        AS total_transactions,
  COUNT(amount)   AS paid_transactions,
  SUM(amount)     AS total_revenue,
  AVG(amount)     AS average_sale,
  SUM(amount) / COUNT(DISTINCT rep) AS revenue_per_rep
FROM sales;

total_transactions │ 8
paid_transactions  │ 7
total_revenue      │ 6550
average_sale       │ 935.71
revenue_per_rep    │ 2183.33

COUNT DISTINCT vs COUNT All

WHAT TO COUNT                       QUERY
──────────────────────────────────────────────────────
All rows (including NULLs)          COUNT(*)
Rows where column is not NULL       COUNT(column)
Unique values in a column           COUNT(DISTINCT column)

Example:
  region column: North,South,North,East,South,East,West,South

  COUNT(*)              → 8
  COUNT(region)         → 8  (none are NULL)
  COUNT(DISTINCT region)→ 4  (North,South,East,West)

Aggregates with WHERE

WHERE filters rows before aggregation. The aggregate function sees only the rows that pass the WHERE condition.

-- Sum only South region sales
SELECT SUM(amount) AS south_revenue
FROM sales
WHERE region = 'South';

1000 + 1300 + NULL = 2100   (NULL skipped)

NULL Behavior in Aggregates

Function    NULL Treatment
──────────────────────────────────────────────
COUNT(*)    Counts every row including NULL rows
COUNT(col)  Skips rows where col is NULL
SUM(col)    Skips NULL values
AVG(col)    Skips NULL values (divides by non-NULL count)

IMPORTANT:
  AVG([1200, 800, NULL]) = (1200 + 800) / 2 = 1000
  NOT (1200 + 800 + 0) / 3 = 666.67

Aggregates Without GROUP BY

Without a GROUP BY clause, aggregate functions return one row summarizing the entire table. Adding GROUP BY creates one summary row per group.

-- Without GROUP BY: one row for the whole table
SELECT SUM(amount) FROM sales;
── 6550

-- With GROUP BY: one row per rep
SELECT rep, SUM(amount)
FROM sales
GROUP BY rep;
── Alice: 2600
── Bob:   2100
── Carol: 1850

Practical Report: Sales Dashboard

SELECT
  rep,
  COUNT(*)           AS deals_closed,
  SUM(amount)        AS total_revenue,
  ROUND(AVG(amount), 2) AS avg_deal_size
FROM sales
WHERE amount IS NOT NULL
GROUP BY rep
ORDER BY total_revenue DESC;

rep   │ deals_closed │ total_revenue │ avg_deal_size
──────┼──────────────┼───────────────┼──────────────
Alice │ 3            │ 2600          │ 866.67
Bob   │ 2            │ 2100          │ 1050.00
Carol │ 2            │ 1850          │ 925.00

Key Takeaways

• COUNT(*) counts all rows; COUNT(col) skips NULLs in that column
• SUM(col) adds all non-NULL values in the column
• AVG(col) divides the sum by the count of non-NULL values
• WHERE filters before aggregation; use it to limit which rows the function sees
• Without GROUP BY, aggregates summarize the entire table into one row