SQL Functions: Aggregate (COUNT, SUM, AVG) & Scalar Functions

1 Aggregate Functions

Aggregate functions take a column of values and return a single value — a count, a total, an average. You already saw them briefly with GROUP BY; now let's master each one.

COUNT — How Many?

SQL

-- How many sales did we make?
SELECT COUNT(*) AS total_sales FROM sales;

Result

total_sales
-----------
7

SQL

-- How many sales per salesperson?
SELECT   salesperson,
         COUNT(*) AS deals
FROM     sales
GROUP BY salesperson
ORDER BY deals DESC;

Result

salesperson    | deals
---------------|------
Sara Ahmed     | 2
James Wilson   | 2
Priya Sharma   | 2
David Chen     | 1

COUNT(*) vs COUNT(column): COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only rows where that column is not NULL. If you want to know how many rows have a phone number: COUNT(phone).

SUM — What's the Total?

SQL

-- Total revenue (amount x quantity for each sale)
SELECT SUM(amount * quantity) AS total_revenue
FROM sales;

Result

total_revenue
-------------
2186.60

SQL

-- Revenue breakdown by region
SELECT   region,
         SUM(amount * quantity) AS revenue,
         SUM(quantity) AS units_sold
FROM     sales
GROUP BY region
ORDER BY revenue DESC;

Result

region      | revenue  | units_sold
------------|----------|----------
Asia        | 1197.72  | 30
Europe      | 489.83   | 17
Middle East | 498.97   | 4

AVG — What's the Average?

SQL

-- Average sale amount
SELECT ROUND(AVG(amount), 2) AS avg_amount
FROM sales;

Result

avg_amount
----------
83.99

SQL

-- Average sale per salesperson
SELECT   salesperson,
         ROUND(AVG(amount * quantity), 2) AS avg_deal_value
FROM     sales
GROUP BY salesperson
ORDER BY avg_deal_value DESC;

Result

salesperson    | avg_deal_value
---------------|---------------
Priya Sharma   | 598.86
Sara Ahmed     | 174.49
James Wilson   | 244.92
David Chen     | 399.80

MAX and MIN — Extremes

SQL

-- Biggest and smallest single sale
SELECT
    MAX(amount) AS highest_price,
    MIN(amount) AS lowest_price,
    MAX(quantity) AS biggest_order,
    MAX(sale_date) AS most_recent
FROM sales;

Result

highest_price | lowest_price | biggest_order | most_recent
--------------|--------------|---------------|----------
199.00        | 19.99        | 20            | 2025-03-28

Combining Everything: A Sales Dashboard Query

SQL

-- Complete sales dashboard by salesperson
SELECT
    salesperson,
    COUNT(*) AS deals,
    SUM(quantity) AS units,
    SUM(amount * quantity) AS revenue,
    ROUND(AVG(amount), 2) AS avg_price,
    MAX(sale_date) AS last_sale
FROM     sales
GROUP BY salesperson
HAVING   SUM(amount * quantity) > 300
ORDER BY revenue DESC;

Result

salesperson    | deals | units | revenue  | avg_price | last_sale
---------------|-------|-------|----------|-----------|----------
Priya Sharma   | 2     | 10   | 797.92   | 124.50    | 2025-03-14
James Wilson   | 2     | 17   | 489.83   | 34.99     | 2025-02-18
Sara Ahmed     | 2     | 4    | 348.97   | 124.50    | 2025-01-22
David Chen     | 1     | 20   | 399.80   | 19.99     | 2025-03-28

Remember: Aggregate functions ignore NULL values. AVG(salary) with values 100, NULL, 200 returns 150 (not 100). If NULLs should count as zero, use AVG(COALESCE(salary, 0)).

Key Takeaways

COUNT(*) = number of rows; COUNT(col) = non-NULL values only
SUM(col) = total; AVG(col) = average; MAX/MIN = extremes
Pair with GROUP BY for per-category/per-person breakdowns
Use HAVING to filter groups after aggregation
All aggregate functions skip NULL values — use COALESCE if you need NULLs counted as zero

2 Scalar Functions

Top

Scalar functions operate on each row individually and return one value per row (unlike aggregate functions which return one value per group). They're used for transforming, formatting, and cleaning data.

String Functions

SQL

SELECT
    salesperson,
    UPPER(salesperson)                     AS uppercase,
    LOWER(salesperson)                     AS lowercase,
    LENGTH(salesperson)                    AS name_length,
    SUBSTRING(salesperson, 1, 5)           AS first_five,
    CONCAT(salesperson, ' - ', region)    AS display_name
FROM sales
WHERE id = 1;

Result

salesperson | uppercase    | lowercase    | name_length | first_five | display_name
------------|--------------|--------------|-------------|------------|----------------------
Sara Ahmed  | SARA AHMED   | sara ahmed   | 10          | Sara       | Sara Ahmed - Middle East

Function	What It Does	Example	Result
`UPPER(str)`	Convert to uppercase	`UPPER('hello')`	`'HELLO'`
`LOWER(str)`	Convert to lowercase	`LOWER('HELLO')`	`'hello'`
`LENGTH(str)`	Count characters	`LENGTH('SQL')`	`3`
`SUBSTRING(str, pos, len)`	Extract portion	`SUBSTRING('Database', 1, 4)`	`'Data'`
`CONCAT(a, b)`	Join strings	`CONCAT('Hello', ' ', 'World')`	`'Hello World'`
`TRIM(str)`	Remove leading/trailing spaces	`TRIM(' hi ')`	`'hi'`
`REPLACE(str, old, new)`	Replace text	`REPLACE('2025-01-10', '-', '/')`	`'2025/01/10'`

Note: MySQL uses UPPER()/LOWER(). SQL Server uses UPPER()/LOWER() too. Some older references use UCASE()/LCASE() which are MySQL aliases for the same thing. Use UPPER/LOWER for portability.

Numeric Functions

SQL

SELECT
    amount,
    ROUND(amount * 1.15, 2)    AS with_tax,
    CEILING(amount)             AS rounded_up,
    FLOOR(amount)               AS rounded_down,
    ABS(-50)                    AS absolute_val,
    amount % 10                 AS remainder
FROM sales
WHERE id IN (1, 4);

Result

amount | with_tax | rounded_up | rounded_down | absolute_val | remainder
-------|----------|------------|--------------|--------------|----------
49.99  | 57.49    | 50         | 49           | 50           | 9.99
19.99  | 22.99    | 20         | 19           | 50           | 9.99

Function	What It Does	Example	Result
`ROUND(n, d)`	Round to `d` decimal places	`ROUND(3.14159, 2)`	`3.14`
`CEILING(n)`	Round up to nearest integer	`CEILING(4.1)`	`5`
`FLOOR(n)`	Round down to nearest integer	`FLOOR(4.9)`	`4`
`ABS(n)`	Absolute value	`ABS(-42)`	`42`

Date and Time Functions

SQL

SELECT
    sale_date,
    NOW()                               AS current_datetime,
    CURDATE()                           AS today,
    YEAR(sale_date)                     AS sale_year,
    MONTH(sale_date)                    AS sale_month,
    DAY(sale_date)                      AS sale_day,
    DAYNAME(sale_date)                  AS day_of_week,
    DATEDIFF(CURDATE(), sale_date)      AS days_ago,
    DATE_FORMAT(sale_date, '%d %b %Y') AS formatted
FROM sales
WHERE id = 1;

Result

sale_date  | current_datetime    | today      | sale_year | sale_month | sale_day | day_of_week | days_ago | formatted
-----------|---------------------|------------|-----------|------------|----------|-------------|----------|----------
2025-01-10 | 2026-04-04 12:00:00 | 2026-04-04 | 2025      | 1          | 10       | Friday      | 449      | 10 Jan 2025

Function	What It Does	Example
`NOW()`	Current date and time	`2026-04-04 12:30:00`
`CURDATE()`	Current date only	`2026-04-04`
`YEAR(d)` / `MONTH(d)` / `DAY(d)`	Extract date parts	`YEAR('2025-03-14')` → `2025`
`DATEDIFF(a, b)`	Days between two dates	`DATEDIFF('2025-03-01', '2025-01-01')` → `59`
`DATE_ADD(d, INTERVAL n unit)`	Add time to a date	`DATE_ADD('2025-01-01', INTERVAL 30 DAY)`
`DATE_FORMAT(d, fmt)`	Format a date for display	`DATE_FORMAT(d, '%d %b %Y')` → `'10 Jan 2025'`

Practical Example: Monthly Sales Report

SQL

-- Monthly revenue report
SELECT
    DATE_FORMAT(sale_date, '%b %Y') AS month,
    COUNT(*) AS sales_count,
    SUM(amount * quantity) AS revenue,
    ROUND(AVG(amount), 2) AS avg_price
FROM     sales
GROUP BY YEAR(sale_date), MONTH(sale_date),
         DATE_FORMAT(sale_date, '%b %Y')
ORDER BY MIN(sale_date);

Result

month    | sales_count | revenue  | avg_price
---------|-------------|----------|----------
Jan 2025 | 2           | 348.97   | 124.50
Feb 2025 | 2           | 489.83   | 34.99
Mar 2025 | 3           | 1197.72  | 89.66

Real-world insight: This is exactly the kind of query that powers dashboards in tools like Metabase, Grafana, or Tableau. A well-written SQL query with the right functions can replace hours of spreadsheet work.

COALESCE and IFNULL — Handling NULL

Two essential functions for dealing with missing data:

SQL

-- Replace NULL with a default value
SELECT
    name,
    COALESCE(phone, 'No phone')  AS phone,
    IFNULL(email, 'N/A')          AS email
FROM customers;

Function	What It Does	Portability
`COALESCE(a, b, c...)`	Returns the first non-NULL argument	Works in all databases (SQL standard)
`IFNULL(a, b)`	If `a` is NULL, return `b`	MySQL only. SQL Server uses `ISNULL`

Always use COALESCE over IFNULL — it's the SQL standard and works on every database. COALESCE can also chain multiple fallbacks: COALESCE(mobile, home_phone, office_phone, 'No number').

Key Takeaways

String: UPPER, LOWER, LENGTH, SUBSTRING, CONCAT, TRIM, REPLACE
Numeric: ROUND, CEILING, FLOOR, ABS
Date: NOW, CURDATE, YEAR/MONTH/DAY, DATEDIFF, DATE_FORMAT
NULL handling: Use COALESCE(col, default) to replace NULLs — it's portable
Combine scalar and aggregate functions for powerful reports (monthly revenue, formatted dashboards)

SQL Functions

In This Chapter

1 Aggregate Functions

COUNT — How Many?

SUM — What's the Total?

AVG — What's the Average?

MAX and MIN — Extremes

Combining Everything: A Sales Dashboard Query

Key Takeaways

2 Scalar Functions

String Functions

Numeric Functions

Date and Time Functions

Practical Example: Monthly Sales Report

COALESCE and IFNULL — Handling NULL

Key Takeaways

All Chapters in SQL Beginner Course

Continue to Intermediate

Courses

Services

Support

Connect