SQL Count(), Avg(), Sum() – Unraveling SQL Aggregation Functions Count(), Avg(), Sum()

Let’s delve into the wonderful world of SQL (Structured Query Language), focusing on three pivotal functions: COUNT, AVG, and SUM.

If you’re managing databases, these functions are crucial for data analysis and manipulation. But don’t worry if you’re a beginner; we’ll guide you through with easy-to-understand examples and sample data.

Before we begin, let’s set the stage with a simple database table called Orders

CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    Product VARCHAR(50),
    Quantity INT,
    Price DECIMAL(5, 2)
);

INSERT INTO Orders (OrderID, CustomerID, Product, Quantity, Price)
VALUES 
    (1, 1001, 'Widget', 10, 20.00),
    (2, 1002, 'Gadget', 5, 50.00),
    (3, 1001, 'Widget', 20, 20.00),
    (4, 1003, 'Doodad', 15, 10.00),
    (5, 1002, 'Gadget', 10, 50.00),
    (6, 1004, 'Thingamajig', 8, 75.00);

This table stores orders made by different customers for various products, each with their quantity and price.

Input Table: Orders

OrderID | CustomerID | Product     | Quantity | Price
--------|------------|-------------|----------|------
1       | 1001       | Widget      | 10       | 20.00
2       | 1002       | Gadget      | 5        | 50.00
3       | 1001       | Widget      | 20       | 20.00
4       | 1003       | Doodad      | 15       | 10.00
5       | 1002       | Gadget      | 10       | 50.00
6       | 1004       | Thingamajig | 8        | 75.00

1) Counting Rows with COUNT()

The COUNT() function returns the number of rows that matches a specified criteria. For example, to know how many orders were made, you would use COUNT() like this

SELECT COUNT(OrderID) AS TotalOrders FROM Orders;

This would return the total number of orders. COUNT(OrderID) counts the number of order IDs, while AS TotalOrders renames the column for the output to be more descriptive.

TotalOrders
-----------
6

What if we wanted to know how many orders each customer made? We can use COUNT() with a GROUP BY statement

SELECT CustomerID, COUNT(OrderID) AS OrdersPerCustomer
FROM Orders
GROUP BY CustomerID;

Output:

CustomerID | OrdersPerCustomer
-----------|------------------
1001       | 2
1002       | 2
1003       | 1
1004       | 1

2) Averaging Values with AVG()

The AVG() function returns the average value of a numeric column. For instance, if we want to know the average quantity of products per order, we can use the following SQL command

SELECT AVG(Quantity) AS AverageQuantity FROM Orders;

This statement will return the average quantity of all orders.

Output:

AverageQuantity
---------------
11.33

To get the average quantity ordered per product, use AVG() with a GROUP BY statement

SELECT Product, AVG(Quantity) AS AverageQuantityPerProduct 
FROM Orders 
GROUP BY Product;

Output:

Product     | AverageQuantityPerProduct
------------|--------------------------
Widget      | 15
Gadget      | 7.5
Doodad      | 15
Thingamajig | 8

3) Summing Up with SUM()

The SUM() function returns the total sum of a numeric column. To know the total quantity of products sold, we could use SUM() as such

SELECT SUM(Quantity) AS TotalQuantity FROM Orders;

This statement will return the total quantity of all orders.

Output:

TotalQuantity
-------------
68

To calculate the total quantity sold per product, we can combine SUM() with GROUP BY

SELECT Product, SUM(Quantity) AS TotalQuantityPerProduct
FROM Orders
GROUP BY Product;

Output:

Product     | TotalQuantityPerProduct
------------|------------------------
Widget      | 30
Gadget      | 15
Doodad      | 15
Thingamajig | 8

A Bonus: Total Sales Per Product

Let’s see how we can combine these functions and other SQL features to get more complex information. For instance, we can calculate the total sales for each product

SELECT Product, SUM(Quantity * Price) AS TotalSales
FROM Orders
GROUP BY Product;

Output:

Product     | TotalSales
------------|-----------
Widget      | 600.00
Gadget      | 750.00
Doodad      | 150.00
Thingamajig | 600.00

Wrapping Up

I hope these examples help you understand how to use the COUNT, AVG, and SUM functions in SQL. As we’ve seen, these aggregation functions are potent tools for extracting meaningful insights from your data. They are especially powerful when used with the GROUP BY statement, allowing you to perform calculations on subsets of your data.