w3resource logo


postgresql avg functions

PostgreSQL AVG function

<<PreviousNext>>

Seondary Nav

Description

The AVG function determines the average of all selected values of a column.

Syntax

AVG (* | [DISTINCT] ALL | column_name)

Parameters

Name Description
column_name Name of the column
* The asterisk(*) indicates all the rows.
DISTINCT This clause is optional. It indicates uniqueness.
ALL This clause is optional. It is default clause.

Table of contents

PostgreSQL AVG function example

PostgreSQL AVG as a level

PostgreSQL AVG function two columns

PostgreSQL AVG with MAX, MIN, COUNT and SUM

PostgreSQL AVG WHERE clause

PostgreSQL AVG with GROUP BY

PostgreSQL AVG with WHERE CLAUSE and GROUP BY

PostgreSQL AVG with HAVING and GROUP BY

PostgreSQL AVG with GROUP BY and ORDER BY

PostgreSQL AVG function example

The sample table

postgresql sample table employee example1

If we want to find the average salary from all employees in employee table, the following SQL can be used.

SQL

SELECT AVG(salary) 
FROM employee;
              

Output

postgresql avg function example1

Pictorial Presentation of PostgreSQL AVG() function

postgresql avg function

Go Top

PostgreSQL AVG as a level

If we want to get the average salary for all employees and show the result against 'Average Salary' head in employee table, the following SQL can be used.

SQL

SELECT AVG(salary) "Average Salary" 
FROM employee;
              

OR

SELECT AVG(DISTINCT salary) "Average Salary" 
FROM employee;
              

OR

SELECT AVG(ALL salary) "Average Salary" 
FROM employee;
              

Output

postgresql avg function as level name

Go Top

PostgreSQL AVG two columns

If we want to get the average salary and deduction from employee table, the following SQL can be used.

SQL

SELECT AVG(salary) "Average Salary" ,
AVG(deduction) "Average Deduction"
FROM employee;
              

Output

postgresql avg function two columns

Go Top

PostgreSQL AVG with MAX, MIN, COUNT and SUM

If we want to get the average salary, maximum salary, minimum salary, total employee and total salary from employee table, the following SQL can be used.

SQL

SELECT AVG(salary) "Average Salary" ,
MAX(salary) "Maximum Salary",
MIN(salary) "Minimum Salary",
COUNT(*) "Total Employee",
SUM(salary) "Total Salary"
FROM employee;
              

Output

postgresql avg function with max,min,count and sum

Go Top

PostgreSQL AVG WHERE clause

If we want to get the average salary and deduction from employee table who belongs to the designation 'SALESMAN', the following SQL can be used.

SQL

SELECT AVG(salary) "Average Salary" ,
AVG(deduction) "Average Deduction"
FROM employee
WHERE designame='SALESMAN';
              

Output

postgresql avg function where clause

Pictorial Presentation of PostgreSQL AVG with WHERE clause

postgresql avg with where

Go Top

PostgreSQL AVG with GROUP BY

Sample table : employees

The following statement will return the designation and average salary against each designation from the employees table.

SQL

SELECT job_id, ROUND(AVG(salary),2) AS "Average Salary rounded" 
FROM employees 
GROUP BY job_id;

Output

   job_id   | Average Salary
------------+----------------
 AC_ACCOUNT |        8300.00
 ST_MAN     |        7280.00
 IT_PROG    |        5760.00
 SA_MAN     |       12200.00
 AD_PRES    |       24000.00
 AC_MGR     |       12000.00
 FI_MGR     |       12000.00
 AD_ASST    |        4400.00
 MK_MAN     |       13000.00
 PU_CLERK   |        2780.00
 HR_REP     |        6500.00
 PR_REP     |       10000.00
 FI_ACCOUNT |        7920.00
 SH_CLERK   |        3215.00
 AD_VP      |       17000.00
 SA_REP     |        8350.00
 ST_CLERK   |        2785.00
 MK_REP     |        6000.00
 PU_MAN     |       11000.00
(19 rows)

Pictorial Presentation of PostgreSQL AVG with GROUP BY

postgresql avg with group by

Go Top

PostgreSQL AVG with WHERE CLAUSE and GROUP BY

Sample table : employees

The following statement will return the designation wise average salary for those designation which salary does not exceed 12000 and above, from employees table.

SQL

SELECT job_id,ROUND(AVG(salary),2) AS "Avereage Salary" 
FROM employees 
WHERE salary<12000 
GROUP BY job_id;

Output

   job_id   | Avereage Salary
------------+-----------------
 AC_ACCOUNT |         8300.00
 ST_MAN     |         7280.00
 IT_PROG    |         5760.00
 SA_MAN     |        10750.00
 AD_ASST    |         4400.00
 PU_CLERK   |         2780.00
 HR_REP     |         6500.00
 PR_REP     |        10000.00
 FI_ACCOUNT |         7920.00
 SH_CLERK   |         3215.00
 SA_REP     |         8350.00
 ST_CLERK   |         2785.00
 MK_REP     |         6000.00
 PU_MAN     |        11000.00
(14 rows)

Go Top

PostgreSQL AVG with HAVING and GROUP BY

Sample table : employees

The following sql statement will return, designation wise total employees and average salary for those designation which salary does not exceed 12000 and above and a minimum of five employees have in this designation.

SQL

SELECT job_id,COUNT(*) AS "Number of Employees", 
ROUND(AVG(salary),2) AS "Average Salary" 
FROM employees 
WHERE salary<12000 
GROUP BY job_id 
HAVING COUNT(*)>=5;

Output

   job_id   | Number of Employees | Average Salary
------------+---------------------+----------------
 ST_MAN     |                   5 |        7280.00
 IT_PROG    |                   5 |        5760.00
 PU_CLERK   |                   5 |        2780.00
 FI_ACCOUNT |                   5 |        7920.00
 SH_CLERK   |                  20 |        3215.00
 SA_REP     |                  30 |        8350.00
 ST_CLERK   |                  20 |        2785.00
(7 rows)			  

Pictorial Presentation of PostgreSQL AVG with HAVING

postgresql avg with group by having

Go Top

PostgreSQL AVG with GROUP BY and ORDER BY

Sample table : employees

The following statement will return, the designation wise total employees and average salary, arranged in descending order according to the total number of employees, for those designation which salary does not exceed 12000 and above and a minimum of five employees have in this designation.

SQL

SELECT job_id,COUNT(*) AS "Number of employees" ,
ROUND(AVG(salary),2) AS "Average Salary" 
FROM employees 
WHERE salary<12000 
GROUP BY job_id 
HAVING COUNT(*)>=5 
ORDER BY COUNT(*) DESC;

Output

   job_id   | Number of employees | Average Salary
------------+---------------------+----------------
 SA_REP     |                  30 |        8350.00
 SH_CLERK   |                  20 |        3215.00
 ST_CLERK   |                  20 |        2785.00
 ST_MAN     |                   5 |        7280.00
 FI_ACCOUNT |                   5 |        7920.00
 IT_PROG    |                   5 |        5760.00
 PU_CLERK   |                   5 |        2780.00
(7 rows)

Go Top



<<PreviousNext>>

Looking for some other tutorial?