Skip to main content

AVG

Returns the average value of all non NULL values within a column or grouping of columns.

See also:
[SELECT]

Syntax​

SELECT AVG(columnExpression) FROM <multipartIdentifier>
[ GROUP BY groupByColumn ];

Arguments​

columnExpression
A column or expression.

String and BLOB values which cannot be represented as numeric values are interpreted as 0.

At least one value from the columnExpression must be non NULL otherwise NULL is returned.

groupByColumn
A column or columns used to perform summary or aggregate operations against. The GROUP BY clause returns one row for each column grouping.

Return Value(s)​

Returns a floating point value representing the average of the columnExpression.

Examples​

Return the average of a column expression over an entire resource​

SELECT avg(json_array_length(disks)) as avg_disks
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';

Return the average of a column expression grouped by another column expression​

SELECT tags, avg(json_array_length(disks)) as avg_disks
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a'
GROUP BY json_extract(tags, '$.instanceType');

Use AVG as a window function to calculate moving averages​

-- Calculate moving average of commit activity
WITH weekly_totals AS (
    SELECT
        week,
        SUM(json_each.value) as commits_this_week
    FROM github.repos.stats_commit_activity, JSON_EACH(days)
    WHERE owner = 'stackql'
      AND repo = 'stackql'
    GROUP BY week
)
SELECT
    week,
    commits_this_week,
    ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg
FROM weekly_totals
ORDER BY week;

For more information, see https://www.sqlite.org/lang_aggfunc.html#avg.