PostgreSQL - SPLessons

PostgreSQL Group Functions

Chapter 12

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Group Functions

PostgreSQL Group Functions

shape Introduction

PostgreSQL Group Functions is a type of command which accepts ‘n’ number 0f values as input and return a single value.

PostgreSQL Group Functions can be of different types:

Sum

shape Description

sum() function is used to calculate the set of values or an expression. For example,the sum function is used to add all the columns details like salaries of employees.

shape Syntax


Select Sum<column_name> from <table_name>;

Table_name => Any accurate table.

Column_name =>The operation that one can perform on a column in the table.

shape Examples

Below is the example to understand the concept of sum function in the table.

SQLDB=# select * from employee22;

   emp_id | ename | sal   | deptno 
 ---------+-------+-------+--------
    1001  | mike  | 14000 |   10 
    1002  | rambo | 25000 |   20 
    1003  | kate  | 15500 |   10 
    1004  | jeo   | 17000 |   20 
    1005  | finn  | 27000 |   30 

(5 rows)

SQLDB=# select sum(sal) from employee22;

   sum(sal) 
  ----------
    98500 

(1 row)

The above example tells that,when sum() functional operation is performed on a column name like salary, then it will add all the employee salaries and gives the output.

Average

shape Description

Average function in PostgreSQL Group Functions is used to calculate the average of a given arrangement of values or an expression. For example the average function is utilized to compute the average of a column name salary in the table.

shape Syntax

Select avg(column_name) from <table_name>;

Table_name => Any accurate table.

Column_name =>The operation that one can perform on a column in the table.


shape Examples

Below is the example to understand the concept of average function in the table.

SQLDB=# select * from employee22;

  emp_id | ename | sal   | deptno 
---------+-------+-------+--------
    1001 | mike  | 14000 |  10 
    1002 | rambo | 25000 |  20 
    1003 | kate  | 15500 |  10 
    1004 | jeo   | 17000 |  20 
    1005 | finn  | 27000 |  30 

(5 rows)
SQLDB=# select avg(sal) from employee22;

  avg(sal)   
------------
  27005     

(1 row)

The above example tells that,when avg() functional operation is performed on a column name like salary,then it will average all the employee salaries and gives the output.

Maximum()

shape Description

Maximum function is utilized to ascertain the most extreme value of a given arrangement of values or an expression. For illustration the maximum function is utilized to locate the maximum of a column name salary in the table.

shape Syntax

Select maximum<column_name>from <table_name>;

Table_name => Any accurate table.

Column_name =>The operation that one can perform on a column in the table.

shape Examples

Below is the example to understand the concept of maximum function in the table.

SQLDB=# select * from employee22;

   emp_id | ename | sal   | deptno 
----------+-------+-------+--------
     1001 | mike  | 14000 |   10 
     1002 | rambo | 25000 |   20 
     1003 | kate  | 15500 |   10 
     1004 | jeo   | 17000 |   20 
     1005 | finn  | 27500 |   30 

(5 rows)

SQLDB=# select max(sal) from employee22;

   max(sal) 
  ----------
    27500 

(1 row)

The above example tells that,when max() functional operation is performed on a column name like salary, then it will display the highest salary of employee.

Minimum()

shape Description

Minimum function is utilized to compute the base estimation of a given arrangement of values or an expression. For illustration the minimum function is utilized to locate the minimum of a column name salary in the table.

shape Syntax

Select minimum<column_name>from <table_name>;

Table_name => Any accurate table.

Column_name => The operation that one can perform on a column in the table.


shape Examples

Below is the example to understand the concept of minimum function in the table.

SQLDB=# select * from employee22;

  emp_id | ename | sal   | deptno 
---------+-------+-------+--------
   1001  | mike  | 14000 |   10 
   1002  | rambo | 25000 |   20 
   1003  | kate  | 15500 |   10 
   1004  | jeo   | 17000 |   20 
   1005  | finn  | 27000 |   30 

(5 rows)

SQLDB=# select min(sal) from employee22;

   min(sal) 
  ---------
    14000 

(1 row)

The above example tells that, when min() functional operation is performed on a column name like salary, then it will display the lowest salary of employee.

Count()

shape Description

Count function is utilized to compute the aggregate estimation of a given set of values or an expression. For illustration the count function is utilized to locate the aggregate number of employees in the table.

shape Syntax

select count<column_name> from <table_name>;

Table_name => Any accurate table.

Column_name => The operation that one can perform on a column in the table.

shape Examples

Below is the example to understand the concept of count function in the table.

SQLDB=# select * from employee22;

  emp_id | ename | sal   | deptno 
---------+-------+-------+--------
    1001 | mike  | 14000 |   10 
    1002 | rambo | 25000 |   20 
    1003 | kate  | 15500 |   10 
    1004 | jeo   | 17000 |   20 
    1005 | finn  | 27000 |   30 

(5 rows)

SQLDB=# select count(emp_id) from employee22;

  count(emp_id) 
 ---------------
             5 
 ---------------
(1 row)

The above example tells that, when count() functional operation is performed on a column name like emp_id, then it will count all the employees in the table and display the output as 5.

Summary

shape Key Points

  • PostgreSQL Group Functions – Group function is used to accept ‘n’ number of values and return a single value.
  • Sum functions – Adds,average calculates the average of set of values.
  • Maximum and minimum functions – Calculates the maximum and minimum values in given set of values.
  • Count – Gives total values.