#CHINOOK DATABASE SQLITE STUDIO HOW TO#

The first four rows are the integer and real values: 1,2, 10.1, and 20.5. Therefore, when calculating the average, the AVG function ignores it and takes 8 rows into the calculation. SELECT avg(val)Īvg_tests Code language: SQL (Structured Query Language) ( sql ) WHERE rowid < 5 Code language: SQL (Structured Query Language) ( sql )įinally, apply the AVG function to all the values in the val column of the avg_tests table.

SELECT rowid,įROM avg_tests Code language: SQL (Structured Query Language) ( sql )Īfter that, you can use the AVG function to calculate the average of the first four rows that contain only numeric values. Then, query data from the avg_tests table. (x '0011') Code language: SQL (Structured Query Language) ( sql ) Next, insert some mixed values into the avg_tests table. You can take a quick test to see how the SQLite function works with various data types.įirst, create a new table named avg_tests using the following statement: CREATE TABLE avg_tests (val) Code language: SQL (Structured Query Language) ( sql ) The AVG function only returns a NULL value if and only if all values in the group are NULL values. The value of the AVG function is always a floating point value or a NULL value. If a column stores mixed data types such as integer, real, BLOB, and text, SQLite AVG function interprets the BLOB that does not look like a number as zero (0).

In case you want to calculate the average value of distinct (or unique) values, you need to specify the DISTINCT clause explicitly in expression. It means the AVG function will take all non-NULL values when it calculates the average value. The following illustrates the syntax of the AVG function: AVG( expression) Code language: SQL (Structured Query Language) ( sql )īy default, the AVG function uses ALL clause whether you specify it or not. The AVG function is an aggregate function that calculates the average value of all non-NULL values within a group.

#CHINOOK DATABASE SQLITE STUDIO HOW TO#

Summary: in this tutorial, you will learn how to use the SQLite AVG function to calculate the average value of a set of values.

Chinook database sqlite studio