DEV Community: Vivek Kumar

SQL Aggregate Functions: Stop Guessing, Start Calculating

Vivek Kumar — Thu, 09 Apr 2026 18:20:27 +0000

SQL Aggregate Functions: Stop Guessing, Start Calculating

If you've ever needed to answer questions like "How many orders did we get this month?", "What's our total revenue?", or "Who's our highest-paid employee?" — you need SQL aggregate functions. They're the workhorses of data analysis, and once you understand them, you'll wonder how you ever got by without them.

In this guide, we'll cover the five essential SQL aggregate functions — COUNT, SUM, AVG, MIN, and MAX — with realistic examples you can actually use in your projects.

What Are Aggregate Functions?

Aggregate functions take a group of rows and return a single value. Instead of seeing one result per row, you get a summary. Think of them as the SQL equivalent of a calculator that works across your entire dataset.

We'll use a sample orders table throughout this article:

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    product_name VARCHAR(100),
    quantity     INT,
    unit_price   DECIMAL(10, 2),
    order_date   DATE,
    status       VARCHAR(20)
);

And some sample data to work with:

INSERT INTO orders (order_id, customer_id, product_name, quantity, unit_price, order_date, status) VALUES
(1,  101, 'Wireless Headphones', 2,  79.99,  '2024-03-01', 'completed'),
(2,  102, 'USB-C Hub',           1,  49.99,  '2024-03-02', 'completed'),
(3,  101, 'Mechanical Keyboard', 1,  129.99, '2024-03-05', 'completed'),
(4,  103, 'Webcam HD',           3,  59.99,  '2024-03-07', 'pending'),
(5,  104, 'Monitor Stand',       1,  39.99,  '2024-03-10', 'completed'),
(6,  102, 'Wireless Mouse',      2,  34.99,  '2024-03-12', 'cancelled'),
(7,  105, 'Laptop Sleeve',       4,  24.99,  '2024-03-15', 'completed'),
(8,  103, 'HDMI Cable',          2,  14.99,  '2024-03-18', 'completed'),
(9,  106, 'Desk Lamp',           1,  44.99,  '2024-03-20', 'pending'),
(10, 101, 'Cable Organizer',     5,   9.99,  '2024-03-22', 'completed');

1. COUNT — How Many Rows Are There?

COUNT answers "how many?" It's arguably the most-used aggregate function in SQL.

Count all rows

SELECT COUNT(*) AS total_orders
FROM orders;

Result:
| total_orders |
|---|
| 10 |

Count non-NULL values in a column

COUNT(column_name) skips NULL values, while COUNT(*) counts every row.

SELECT COUNT(order_id) AS orders_with_id
FROM orders;

Count with a filter

How many orders were completed?

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed';

Result:
| completed_orders |
|---|
| 7 |

Count distinct values

How many unique customers placed orders?

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result:
| unique_customers |
|---|
| 6 |

2. SUM — Add It All Up

SUM totals up numeric values. Perfect for revenue calculations, quantity totals, and similar tasks.

Total quantity sold

SELECT SUM(quantity) AS total_items_sold
FROM orders;

Result:
| total_items_sold |
|---|
| 22 |

Total revenue from completed orders

Here we calculate revenue by multiplying quantity × unit_price for each row, then summing it all:

SELECT SUM(quantity * unit_price) AS total_revenue
FROM orders
WHERE status = 'completed';

Result:
| total_revenue |
|---|
| 704.81 |

Revenue per customer

This is where SUM gets really powerful when combined with GROUP BY:

SELECT 
    customer_id,
    SUM(quantity * unit_price) AS customer_revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY customer_revenue DESC;

Result:
| customer_id | customer_revenue |
|---|---|
| 101 | 369.95 |
| 105 | 99.96 |
| 103 | 29.98 |
| 104 | 39.99 |
| 102 | 49.99 |

Customer 101 is clearly your best customer!

3. AVG — Find the Average

AVG calculates the mean of a set of values. Great for understanding typical behavior.

Average order value

SELECT AVG(unit_price) AS avg_product_price
FROM orders;

Result:
| avg_product_price |
|---|
| 48.99 |

Average items per order

SELECT AVG(quantity) AS avg_items_per_order
FROM orders;

Result:
| avg_items_per_order |
|---|
| 2.2000 |

Average revenue per completed order

SELECT ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
FROM orders
WHERE status = 'completed';

Result:
| avg_order_value |
|---|
| 100.69 |

Pro tip: AVG ignores NULL values automatically. If a column has NULLs, they won't drag down your average — but be aware of this behavior to avoid surprises.

4. MIN — Find the Smallest Value

MIN returns the lowest value in a column. Works with numbers, dates, and even strings.

Cheapest product

SELECT MIN(unit_price) AS cheapest_product_price
FROM orders;

Result:
| cheapest_product_price |
|---|
| 9.99 |

Earliest order date

SELECT MIN(order_date) AS first_order_date
FROM orders;

Result:
| first_order_date |
|---|
| 2024-03-01 |

First order per customer

SELECT 
    customer_id,
    MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id;

Result:
| customer_id | first_order_date |
|---|---|
| 101 | 2024-03-01 |
| 102 | 2024-03-02 |
| 103 | 2024-03-07 |
| 104 | 2024-03-10 |
| 105 | 2024-03-15 |
| 106 | 2024-03-20 |

5. MAX — Find the Largest Value

MAX is the mirror image of MIN — it returns the highest value.

Most expensive product

SELECT MAX(unit_price) AS most_expensive_product
FROM orders;

Result:
| most_expensive_product |
|---|
| 129.99 |

Most recent order date

SELECT MAX(order_date) AS last_order_date
FROM orders;

Result:
| last_order_date |
|---|
| 2024-03-22 |

Biggest single order value per customer

SELECT 
    customer_id,
    MAX(quantity * unit_price) AS biggest_order_value
FROM orders
GROUP BY customer_id
ORDER BY biggest_order_value DESC;

Combining Multiple Aggregates

The real magic happens when you use several aggregate functions together in one query. Here's a full sales summary report:

SELECT 
    COUNT(*)                              AS total_orders,
    COUNT(DISTINCT customer_id)           AS unique_customers,
    SUM(quantity)                         AS total_items_sold,
    ROUND(SUM(quantity * unit_price), 2)  AS gross_revenue,
    ROUND(AVG(quantity * unit_price), 2)  AS avg_order_value,
    MIN(unit_price)                       AS cheapest_item,
    MAX(unit_price)                       AS priciest_item
FROM orders
WHERE status = 'completed';

Result:
| total_orders | unique_customers | total_items_sold | gross_revenue | avg_order_value | cheapest_item | priciest_item |
|---|---|---|---|---|---|---|
| 7 | 5 | 15 | 704.81 | 100.69 | 9.99 | 129.99 |

One query, a full business snapshot. That's the power of aggregate functions.

Common Mistakes to Avoid

1. Forgetting to use GROUP BY when selecting non-aggregated columns

This will throw an error in most databases:

-- ❌ Wrong — product_name isn't aggregated or grouped
SELECT product_name, SUM(quantity)
FROM orders;

-- ✅ Correct
SELECT product_name, SUM(quantity) AS total_sold
FROM orders
GROUP BY product_name;

2. Using WHERE instead of HAVING to filter aggregated results

WHERE filters rows before aggregation. HAVING filters after. If you want to filter based on an aggregate result, you need HAVING:

-- ❌ Wrong — can't use SUM() in WHERE
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE SUM(quantity * unit_price) > 100
GROUP BY customer_id;

-- ✅ Correct
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(quantity * unit_price) > 100;

3. Confusing COUNT(*) and COUNT(column)

COUNT(*) counts all rows including NULLs. COUNT(column) skips NULLs in that column. Choose wisely depending on what you actually want to count.

4. Not accounting for NULL in AVG

If some rows have NULL in the column you're averaging, those rows are excluded from both the sum and the count. This can make your average higher than expected.

-- Example: if 3 out of 10 rows have NULL discount, AVG only divides by 7
SELECT AVG(discount_amount) FROM orders;

Key Takeaways

COUNT — Count rows or distinct values. Use COUNT(*) for all rows, COUNT(col) to skip NULLs.
SUM — Add up numeric values. Combine with GROUP BY for per-group totals.
AVG — Calculate the mean. Remember it ignores NULLs.
MIN / MAX — Find the smallest or largest value. Works on numbers, dates, and strings.
Combine multiple aggregates in a single query for powerful summary reports.
Use HAVING (not WHERE) to filter results based on aggregate values.

These five functions are the foundation of nearly every analytical SQL query you'll ever write. Master them, and you'll be answering data questions that used to take spreadsheets and manual work — all with a few clean lines of SQL.

Have a question about aggregate functions, or a favorite use case I didn't cover? Drop it in the comments — I'd love to hear how you're using these in your own projects!

SQL WHERE Clause: The Complete Guide to Filtering Data

Vivek Kumar — Thu, 09 Apr 2026 18:07:05 +0000

SQL WHERE Clause: The Complete Guide to Filtering Data

Imagine you have a database with a million customers, but you only need the ones in New York who signed up last year. Without the WHERE clause, you'd be drowning in data you don't care about. With it, you can slice through your data with surgical precision.

The WHERE clause is one of the first things every SQL developer learns — and one of the most powerful tools you'll use every single day. In this guide, we'll go beyond the basics and cover everything you need to filter data confidently.

Setup: Our Sample Tables

We'll use two tables throughout this guide:

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    city          VARCHAR(50),
    age           INT,
    signup_date   DATE,
    is_active     BOOLEAN
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    product       VARCHAR(100),
    amount        DECIMAL(10,2),
    order_date    DATE,
    status        VARCHAR(20)
);

INSERT INTO customers VALUES
(1,  'Alice',   'Johnson', 'alice@email.com',   'New York',    32, '2022-03-15', true),
(2,  'Bob',     'Smith',   'bob@email.com',     'Chicago',     27, '2023-07-01', true),
(3,  'Carol',   'Davis',   'carol@email.com',   'New York',    45, '2021-11-20', false),
(4,  'David',   'Wilson',  'david@email.com',   'Los Angeles', 31, '2023-01-10', true),
(5,  'Eva',     'Martinez','eva@email.com',     'Chicago',     29, '2022-09-05', true),
(6,  'Frank',   'Brown',   'frank@email.com',   'New York',    52, '2020-06-12', false),
(7,  'Grace',   'Lee',     'grace@email.com',   'Houston',     24, '2023-11-03', true),
(8,  'Henry',   'Taylor',  'henry@email.com',   'Los Angeles', 38, '2022-04-22', true);

Basic Comparisons

The WHERE clause uses comparison operators to filter rows. Here are all the ones you'll regularly use:

Operator	Meaning
`=`	Equal to
`<>` or `!=`	Not equal to
`<`	Less than
`>`	Greater than
`<=`	Less than or equal to
`>=`	Greater than or equal to

Examples

Find all active customers:

SELECT first_name, last_name, city
FROM customers
WHERE is_active = true;

Result: Alice, Bob, David, Eva, Grace, Henry

Find customers aged 30 or older:

SELECT first_name, age, city
FROM customers
WHERE age >= 30;

Result:
| first_name | age | city |
|---|---|---|
| Alice | 32 | New York |
| Carol | 45 | New York |
| David | 31 | Los Angeles |
| Frank | 52 | New York |
| Henry | 38 | Los Angeles |

Combining Conditions: AND, OR, NOT

Real-world queries almost always involve multiple conditions.

AND — both conditions must be true

Active customers in New York:

SELECT first_name, last_name
FROM customers
WHERE city = 'New York'
  AND is_active = true;

Result: Alice Johnson

OR — at least one condition must be true

Customers in New York OR Chicago:

SELECT first_name, city
FROM customers
WHERE city = 'New York'
   OR city = 'Chicago';

Result: Alice (New York), Bob (Chicago), Carol (New York), Eva (Chicago), Frank (New York)

NOT — negate a condition

Customers NOT from New York:

SELECT first_name, city
FROM customers
WHERE NOT city = 'New York';

Combining AND and OR (use parentheses!)

Find active customers who are either from New York or under 30:

-- Without parentheses — this may not do what you expect!
SELECT first_name, age, city
FROM customers
WHERE is_active = true AND city = 'New York' OR age < 30;

-- With parentheses — clear and correct
SELECT first_name, age, city
FROM customers
WHERE is_active = true
  AND (city = 'New York' OR age < 30);

The second version is explicit: it finds active customers where either city is New York or age is under 30. AND binds tighter than OR in SQL, just like multiplication binds tighter than addition in math — parentheses make your intent unmistakable.

IN — Match Any Value in a List

Instead of chaining multiple OR conditions, use IN:

-- Verbose version
SELECT first_name, city
FROM customers
WHERE city = 'New York' OR city = 'Chicago' OR city = 'Houston';

-- Clean version with IN
SELECT first_name, city
FROM customers
WHERE city IN ('New York', 'Chicago', 'Houston');

Both return the same result, but IN is much cleaner. You can also negate it:

-- Customers NOT from these cities
SELECT first_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago');

Result: David (Los Angeles), Grace (Houston), Henry (Los Angeles)

BETWEEN — Range Filtering

BETWEEN checks if a value falls within an inclusive range. Works with numbers, dates, and strings.

Numeric range

Customers aged 25–35:

SELECT first_name, age
FROM customers
WHERE age BETWEEN 25 AND 35;

Result: Alice (32), Bob (27), David (31), Eva (29)

Note: BETWEEN x AND y is inclusive on both ends — equivalent to >= x AND <= y.

Date range

Customers who signed up in 2022:

SELECT first_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2022-01-01' AND '2022-12-31';

Result: Alice (2022-03-15), Eva (2022-09-05), Henry (2022-04-22)

IS NULL / IS NOT NULL

NULL in SQL means "unknown" or "missing data." You can't use = NULL to check for it — you must use IS NULL:

-- This NEVER works (always returns 0 rows)
SELECT * FROM customers WHERE email = NULL;

-- This is correct
SELECT * FROM customers WHERE email IS NULL;

Let's add a customer with a missing email to demonstrate:

INSERT INTO customers VALUES
(9, 'Ian', 'Clark', NULL, 'Boston', 33, '2023-05-10', true);

-- Find customers with no email on file
SELECT first_name, last_name
FROM customers
WHERE email IS NULL;

Result: Ian Clark

-- Find customers who DO have an email
SELECT COUNT(*) AS customers_with_email
FROM customers
WHERE email IS NOT NULL;

LIKE — Pattern Matching

LIKE lets you search for patterns in text using two wildcards:

% — matches any sequence of characters (including none)
_ — matches exactly one character

Find emails from Gmail:

SELECT first_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

Find customers whose first name starts with a vowel (A or E):

SELECT first_name
FROM customers
WHERE first_name LIKE 'A%'
   OR first_name LIKE 'E%';

Result: Alice, Eva

Find customers with a 5-letter first name:

SELECT first_name
FROM customers
WHERE first_name LIKE '_____';

Result: Alice, Grace, Henry, Frank (5 underscores = 5 characters)

Case sensitivity: LIKE is case-sensitive in PostgreSQL but case-insensitive in MySQL. In PostgreSQL, use ILIKE for case-insensitive matching.

Putting It All Together

Here's a real-world query combining multiple WHERE techniques — find active customers aged 25–40 who live in New York or Chicago, and whose email address we have on file:

SELECT 
    first_name,
    last_name,
    age,
    city,
    email
FROM customers
WHERE is_active = true
  AND age BETWEEN 25 AND 40
  AND city IN ('New York', 'Chicago')
  AND email IS NOT NULL
ORDER BY age ASC;

Result:
| first_name | last_name | age | city | email |
|---|---|---|---|---|
| Bob | Smith | 27 | Chicago | bob@email.com |
| Eva | Martinez | 29 | Chicago | eva@email.com |
| Alice | Johnson | 32 | New York | alice@email.com |

One clean query, perfectly targeted results.

Common Mistakes to Avoid

1. Using = NULL instead of IS NULL

-- ❌ Always returns zero rows
WHERE discount_code = NULL

-- ✅ Correct
WHERE discount_code IS NULL

2. Forgetting parentheses with AND/OR

-- ❌ Might not mean what you think
WHERE status = 'active' AND city = 'NY' OR city = 'LA'

-- ✅ Explicit and correct
WHERE status = 'active' AND (city = 'NY' OR city = 'LA')

3. Doing math on the filtered column (kills index performance)

-- ❌ Forces a full table scan — can't use an index on order_date
WHERE YEAR(order_date) = 2023

-- ✅ Uses index on order_date
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'

4. Using LIKE with a leading wildcard (also kills index performance)

-- ❌ Can't use index — scans every row
WHERE email LIKE '%@gmail.com'

-- ✅ Can use index — only scans rows starting with 'alice'
WHERE email LIKE 'alice%'

Key Takeaways

Use comparison operators (=, <>, <, >, etc.) for basic filtering.
Combine conditions with AND, OR, and NOT — use parentheses when mixing them.
IN is cleaner than multiple OR conditions for matching a list of values.
BETWEEN provides inclusive range filtering for numbers and dates.
Always use IS NULL or IS NOT NULL — never = NULL.
LIKE with % and _ enables pattern matching; avoid leading wildcards on large tables.

The WHERE clause is the foundation of meaningful SQL queries. Mastering it means you can always get exactly the data you need — no more, no less.

Got a tricky filtering scenario you've run into? Drop it in the comments — I'd love to help work through it!