Aggregate functions (COUNT, SUM, AVG, MAX, MIN) in DBMS Theory - Full Explanation

Introduction

When working with data, we often want to find overall information like totals, averages, or counts instead of looking at each item separately. Aggregate functions help us quickly get these summary results from many data points.

Explanation

COUNT

COUNT calculates how many items or rows exist in a dataset or group. It can count all rows or only those with non-empty values in a specific column. This helps understand the size or number of entries in data.

COUNT tells you how many items are in your data or group.

SUM

SUM adds up all the numeric values in a column for the selected rows. It gives the total amount or quantity, useful for things like total sales or total hours worked.

SUM gives the total of all numbers in a group.

AVG

AVG calculates the average value by adding all numbers and dividing by the count of those numbers. It shows the typical or central value in a dataset, like average price or average score.

AVG finds the typical value by averaging numbers.

MAX

MAX finds the largest value in a column among the selected rows. It helps identify the highest number, such as the maximum salary or highest temperature recorded.

MAX shows the biggest number in your data.

MIN

MIN finds the smallest value in a column among the selected rows. It helps identify the lowest number, like the minimum price or lowest score.

MIN shows the smallest number in your data.

Real World Analogy

Imagine you have a jar of different colored marbles with numbers on them. You want to know how many marbles there are, the total of all numbers, the average number, the biggest number, and the smallest number. Aggregate functions do this kind of summary for data.

COUNT → Counting how many marbles are in the jar.

SUM → Adding all the numbers on the marbles together.

AVG → Finding the average number by dividing the total by the number of marbles.

MAX → Picking the marble with the highest number.

MIN → Picking the marble with the lowest number.

Diagram

Diagram

┌───────────────┐
│   Data Table  │
│  (many rows)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Aggregate     │
│ Functions     │
│ ┌───────────┐ │
│ │ COUNT     │ │
│ │ SUM       │ │
│ │ AVG       │ │
│ │ MAX       │ │
│ │ MIN       │ │
│ └───────────┘ │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Summary Result│
│ (one value)   │
└───────────────┘

This diagram shows how aggregate functions take many rows of data and produce a single summary value.

Key Facts

COUNT → Returns the number of rows or non-null values in a column.

SUM → Calculates the total sum of numeric values in a column.

AVG → Computes the average (mean) of numeric values in a column.

MAX → Finds the highest value in a column.

MIN → Finds the lowest value in a column.

Code Example

DBMS Theory

import sqlite3

conn = sqlite3.connect(':memory:')
cur = conn.cursor()

cur.execute('CREATE TABLE sales (id INTEGER, amount INTEGER)')
cur.executemany('INSERT INTO sales VALUES (?, ?)', [(1, 100), (2, 200), (3, 150), (4, None)])

cur.execute('SELECT COUNT(amount), SUM(amount), AVG(amount), MAX(amount), MIN(amount) FROM sales')
result = cur.fetchone()
print(f"COUNT: {result[0]}")
print(f"SUM: {result[1]}")
print(f"AVG: {result[2]}")
print(f"MAX: {result[3]}")
print(f"MIN: {result[4]}")

Common Confusions

COUNT counts all rows including those with empty or null values.

COUNT counts all rows including those with empty or null values. COUNT(*) counts all rows, but COUNT(column) counts only rows where the column is not null.

SUM and AVG can be used on any data type.

SUM and AVG can be used on any data type. SUM and AVG only work on numeric data types; using them on text or dates causes errors.

MAX and MIN always return the largest or smallest number.

MAX and MIN always return the largest or smallest number. MAX and MIN return the largest or smallest value based on the data type, so for text they compare alphabetically.

Summary

Aggregate functions help summarize many rows of data into single meaningful values like counts, totals, averages, maximums, and minimums.

COUNT counts rows or non-null values, SUM adds numbers, AVG finds the average, MAX finds the largest, and MIN finds the smallest value.

These functions ignore null values except COUNT(*) which counts all rows.