Yandex Cloud
  • Services
  • Solutions
  • Why Yandex Cloud
  • Blog
  • Pricing
  • Documentation
  • Contact us
Get started
Language / Region
Yandex project
© 2023 Intertech Services AG
Yandex DataLens
  • Getting started
  • Tutorials
  • Concepts
  • Step-by-step guides
  • Access management
  • Pricing policy
  • Visualization reference
  • Function reference
    • All Functions
    • Aggregate functions
    • Array functions
    • Date/Time functions
    • Logical functions
    • Mathematical functions
    • Operators
    • String functions
    • Text markup functions
    • Time series functions
    • Type conversion functions
    • Window functions
      • Overview
      • AVG
      • AVG_IF
      • COUNT
      • COUNT_IF
      • FIRST
      • LAG
      • LAST
      • MAVG
      • MAX
      • MCOUNT
      • MIN
      • MMAX
      • MMIN
      • MSUM
      • RANK
      • RANK_DENSE
      • RANK_PERCENTILE
      • RANK_UNIQUE
      • RAVG
      • RCOUNT
      • RMAX
      • RMIN
      • RSUM
      • SUM
      • SUM_IF
    • Function Availability
  • Function tutorials
  • Courses
  • Troubleshooting
    • Questions and answers
    • DataLens errors
  1. Function reference
  2. Window functions
  3. AVG

AVG (window)

Written by
Yandex Cloud

Function AVG is also found in the following categories: Aggregate functions.

Syntax

Standard
Extended
AVG( value
     TOTAL | WITHIN ... | AMONG ...
   )

More info:

  • TOTAL, WITHIN, AMONG
AVG( value
     TOTAL | WITHIN ... | AMONG ...
     [ BEFORE FILTER BY ... ]
   )

More info:

  • TOTAL, WITHIN, AMONG
  • BEFORE FILTER BY

Description

Returns the average of all values. Applicable to numeric data types.

Argument types:

  • value — Any

Return type: Fractional number

Example

Source data

Date City Category Orders Profit
'2019-03-01' 'London' 'Office Supplies' 8 120.80
'2019-03-04' 'London' 'Office Supplies' 2 100.00
'2019-03-05' 'London' 'Furniture' 1 750.00
'2019-03-02' 'Moscow' 'Furniture' 2 1250.50
'2019-03-03' 'Moscow' 'Office Supplies' 4 85.00
'2019-03-01' 'San Francisco' 'Office Supplies' 23 723.00
'2019-03-01' 'San Francisco' 'Furniture' 1 1000.00
'2019-03-03' 'San Francisco' 'Furniture' 4 4000.00
'2019-03-02' 'Detroit' 'Furniture' 5 3700.00
'2019-03-04' 'Detroit' 'Office Supplies' 25 1200.00
'2019-03-04' 'Detroit' 'Furniture' 2 3500.00

Grouped by [City], [Category].

Sorted by [City], [Category].

Result

[City] [Category] SUM([Orders]) AVG(SUM([Orders]) TOTAL) AVG(SUM([Orders]) WITHIN [City]) AVG(SUM([Orders]) AMONG [City])
'Detroit' 'Furniture' 7 9.62 16.00 3.75
'Detroit' 'Office Supplies' 25 9.62 16.00 15.50
'London' 'Furniture' 1 9.62 5.50 3.75
'London' 'Office Supplies' 10 9.62 5.50 15.50
'Moscow' 'Furniture' 2 9.62 3.00 3.75
'Moscow' 'Office Supplies' 4 9.62 3.00 15.50
'San Francisco' 'Furniture' 5 9.62 14.00 3.75
'San Francisco' 'Office Supplies' 23 9.62 14.00 15.50

Data source support

ClickHouse 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, Oracle Database 12c (12.1), PostgreSQL 9.3.

Was the article helpful?

Language / Region
Yandex project
© 2023 Intertech Services AG