PostgreSQL COUNT, AVG, and SUM Functions
COUNT
The COUNT()
function returns the number of rows that matches a specified criterion.
If the specified criterion is a column name,
the COUNT() function returns the number of columns with that name.
Example
Return the number of customers from the customers table:
postgres=#
postgres=#
SELECT COUNT(customer_id)
FROM customers;
Run
Example »
Note: NULL values are not counted.
By specifying a WHERE clause, you can e.g. return the number of customers
that comes from London:
Example
Return the number of customers from London:
postgres=#
postgres=#
postgres=#
SELECT COUNT(customer_id)
FROM customers
WHERE city = 'London';
Run
Example »
AVG
The AVG()
function returns the average value of a numeric column.
Example
Return the averarge price of all the products in the products table:
postgres=#
postgres=#
SELECT AVG(price)
FROM products;
Run Example
»
Note: NULL values are ignored.
With 2 Decimals
The above example returned the average price of all products, the result was
28.8663636363636364.
We can use the ::NUMERIC operator to round the average price to a number
with 2 decimals:
Example
Return the averarge price of all the products, rounded to 2 decimals:
postgres=#
postgres=#
SELECT AVG(price)::NUMERIC(10,2)
FROM products;
Run
Example »
SUM
The SUM()
function returns the total sum of a numeric column.
The following SQL statement finds the sum of the
quantity fields in the
order_details table:
Example
Return the total amount of ordered items:
postgres=#
postgres=#
SELECT SUM(quantity)
FROM order_details;
Run Example
»
Note: NULL values are ignored.