AVG function - Amazon Redshift

AVG function

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)