Aggregate Functions
PostgreSQL, like other RDBMS products, supports standard aggregate functions. Aggregate functions allows you to compute value from multiple rows and return a single result. Those functions includes: Min, Max, Count, Avg and Sum. Let's take a look at every functions in detail.
Min and Max functions
Min and Max functions, as their names imply, allows you to determine the minimum and maximum values from a set of values. For examples, you can find the highest sell price of item in the
item table by executing the following query:
SELECT MAX(sell_price)
FROM item
You can also find the lowest price of item by using Min function as follows:
SELECT MIN(sell_price)
FROM item
Count Function
Count function allows you to count a number of record in a result set return from a SELECT statement. Here is the syntax of using Count function:
SELECT COUNT(*)
FROM table_name
SELECT COUNT(column_name)
FROM table_name
- In the first query, the count function returns a number of record returned from the table table_name.
- And the second query, the count function returns number of record, which has column_name value not NULL, returned from the table table_name.
For example, you can use count function to count a number of item in the
item table by executing the following query:
SELECT COUNT(*)
FROM item
Sum Function
Sum functions allows you to calculate total values of numeric column in a result set. For example you can calculate total value of sell price of items in
item table as follows:
SELECT SUM(sell_price)
FROM item
AVG Function
AVG function allows you to calculate the average value of numeric column of a result set. For instance you can calculate the average sell price of item in the
item table as follows:
SELECT AVG(sell_price)
FROM item
Aggregate functions mostly uses with GROUP BY clause of select statement to generate the summary. In the next tutorial, you will learn how to use GROUP BY and HAVING clause with SELECT statement to understand more aggregate functions' usages.