Sometimes, instead of listing the records on the tables, we need to perform calculations on the records and list the results. Therefore, we may need to return only mathematical results without returning all records. SQL provides special functions for such cases. These functions are called Aggregate Function (Calculation Functions).
So what do these functions offer us; finding the sum of all values in the column, finding the average of all values in the column, finding the largest or smallest value in the column, calculating the total number of records.
There are some considerations when using these functions. These points are:
- AVG and SUM functions can only be used on numeric values.
- MIN, MAX and COUNT functions can be used on numeric, string and temporary data columns.
- These functions cannot be used on columns of TEXT, NTEXT and IMAGE data types.
- These functions do not take NULL values into account, they ignore them. This is especially important for COUNT operations.
- If (*) is used with COUNT, we can count the data of all rows (even if there is NULL data in the column).
- A query using the aggregation function is now a query with an aggregation function.
USING SUM()
The SUM() function obtains the sum of the values in the specified field. For example, it can be used to find the total value of the products we have, or we can find the total number of stocks we have.
It can only be used in numeric fields, so it cannot be used in data types such as text and date. Also, the “SUM()” function does not only consider NULL values. If NULL values must be taken into account, the expression “SUM(COALESCE(col_name, 0))” must be used instead of “SUM(col_name)”. This expression performs the addition by replacing the NULL values in the “col_name” column with 0.
SELECT SUM(col_name)
FROM table_name
WHERE condition;- For example, let’s find out how many goods were ordered in total.
SELECT SUM(Quantity) AS Product_Sum
FROM [Order Details]
MIN() USAGE
The MIN() function returns the smallest value in the specified field. Imagine that we have 100s of product records in our table. If we want to find the lowest price, we can check the prices one by one and find the cheapest price. However, this will lead to a huge waste of time. Instead, we can reach the result directly with a sql code. In the same way, the head of the accounting department or the general manager of the company may want to see the lowest salary paid to the staff. In this case, we can also get the result with a sql code.
It can be used not only in numeric fields but also in textual fields. In this case, it will sort the textual data from A to Z and return the first record (records starting with A).
SELECT MIN (col_name)
FROM table_name
WHERE condition;- Let’s list the product with the smallest price in the Products table.
SELECT MIN(UnitPrice) AS Minimum_Price
FROM Products
MAX() USAGE
The MAX() function returns the largest value in the specified field. It can be used not only in numeric fields but also in textual fields. In this case it will sort the textual data from A to Z and return the last record.
SELECT MAX (sütun_adi)
FROM tablo_adi
WHERE koşul;- Ürünler (Products) tablosundaki en yüksek fiyatlı ürünü listeleyelim.
SELECT MAX(UnitPrice) AS Maksimum_Fiyat
FROM Products
USING AVG()
AVG() function obtains the average of the values in the specified area. It can be used to find the average value of the products we have.
It can only be used in numeric fields.
SELECT AVG (col_name)
FROM table_name
WHERE condition;- For example, let’s want to get the average price of the individual values of the products of the supplier with record number 22.
SELECT AVG(DISTINCT UnitPrice) AS Average_Price
FROM Products
WHERE SupplierID = 22
COUNT() USAGE
In the article we published before, we examined the use of the count() function in more detail. If you want to take a look, you can click on the link
Returns the number of rows listed according to the specified conditions.
SELECT COUNT (col_name)
from table_name
where condition;- Let’s find out the number of products belonging to the supplier whose SupplierID is 5.
SELECT COUNT(*) AS Number_of_Produtcs
FROM Products
WHERE SupplierID = 5
COUNT() DISTINCT USAGE
When used with COUNT, DISTINCT returns the number of unique records in the specified column.
SELECT COUNT (DISTIMCT col_name)
from table_name
where condition;- As our last example, let’s find out how many different customers orders are received from.
SELECT COUNT (DISTINCT CustomerID)
FROM Orders
