SQL SUM() AND AVG()

In SQL, the SUM() and AVG() functions are used to calculate total and average values in numeric columns.

SQL SUM() Function

The SQL SUM() function is used to calculate the cumulative sum of numeric values in a column. It has the following syntax:

SELECT SUM(column_name)
FROM table;

Here,

• SUM is the function that returns the cumulative sum of numeric values
• column_name is the column to which we apply the SUM function
• table is the name of the table to fetch the data from

For example,

--select the sum of amount from Orders table
SELECT SUM(amount) AS total_sales
FROM Orders;

Here, the SQL command returns the sum of amount of all orders.

Example 1: SQL SUM() Function

Let's take a look at another example.

--select the sum of the amount of id 4 from orders
SELECT SUM(amount) AS total_of_cus4
FROM Orders
WHERE customer_id = 4;

Here, the SQL command returns the total amount to be paid by the customer having id 4.

SQL AVG() Function

The SQL AVG() function is used to calculate the average of numeric values in a column. It has the following syntax:

SELECT AVG(column_name)
FROM table;

Here,

• AVG is the function that returns the aggregate of numeric values
• column_name is the column to which we apply the AVG function
• table is the name of the table to fetch the data from

For example,

-- get average age of customers
SELECT AVG(age) AS average_age
FROM Customers;

Here, the SQL command returns the average age of all customers.

Example 2: SQL AVG() Function

--selects the average amount spent by each customer from the Orders table
SELECT customer_id, AVG(amount) AS average_spends
FROM Orders
GROUP BY customer_id;

Here, the SQL command returns the average spending of each customer.

Recommended Readings:

• SQL Aliases
• SQL GROUP BY
• SQL DISTINCT