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.
