The AVG function returns the average (arithmetic mean) of the input expression values. The AVG function works with numeric values and ignores NULL values.
Syntax
AVG ( [ DISTINCT | ALL ] expression )
Arguments
- expression
-
The target column or expression that the function operates on. The expression is one of the following data types:
SMALLINT
INTEGER
BIGINT
NUMERIC
DECIMAL
REAL
DOUBLE PRECISON
SUPER
- DISTINCT | ALL
-
With the argument DISTINCT, the function eliminates all duplicate values from the specified expression before calculating the average. With the argument ALL, the function retains all duplicate values from the expression for calculating the average. ALL is the default.
Data types
The argument types supported by the AVG function are SMALLINT, INTEGER, BIGINT, NUMERIC, DECIMAL, REAL, DOUBLE PRECISION, and SUPER.
The return types supported by the AVG function are:
-
BIGINT for any integer type argument
-
DOUBLE PRECISION for a floating point argument
Returns the same data type as expression for any other argument type.
The default precision for an AVG function result with a NUMERIC or DECIMAL argument is 38. The scale of the result is the same as the scale of the argument. For example, an AVG of a DEC(5,2) column returns a DEC(38,2) data type.
Examples
Find the average quantity sold per transaction from the SALES table:
select avg(qtysold)from sales;
avg
-----
2
(1 row)Find the average total price listed for all listings:
select avg(numtickets*priceperticket) as avg_total_price from listing;
avg_total_price
-----------------
3034.41
(1 row)Find the average price paid, grouped by month in descending order:
select avg(pricepaid) as avg_price, month
from sales, date
where sales.dateid = date.dateid
group by month
order by avg_price desc;
avg_price | month
-----------+-------
659.34 | MAR
655.06 | APR
645.82 | JAN
643.10 | MAY
642.72 | JUN
642.37 | SEP
640.72 | OCT
640.57 | DEC
635.34 | JUL
635.24 | FEB
634.24 | NOV
632.78 | AUG
(12 rows)