ABS() and AVG() function in SQLite
1. ABS() Function :
ABS(X) is a math function used to get the absolute value of a number.
The ABS(X) function takes a numeric argument X and returns its magnitude.
If the argument X is a blob or string, the function gives 0.0 as the return value
If the argument X is NULL the function returns NULL.
2. AVG() Function :
AVG(X) is an aggregate function used to calculate the average on a set of rows.
The function yields a floating point value equal to the average of all NON-NULL values in the argument group X.
If there is a NULL value passed to the AVG() function, only the non-NULL values are used in calculating the average.
Strings and blobs present in the argument X are evaluated as 0.
The function returns a NULL if all the input values are NULL.
Now, we are going to use a real database example. First, let’s create a table called students with columns name and age and insert some rows!
--create a table CREATE TABLE students( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255), age INT); --insert multiple rows INSERT INTO students(name,age) VALUES ('Mark',20), ('Mary',19), ('John',17), ('Lydia',25);
We’ll then write a SELECT statement that gives us the average age for all the students using the AVG() function
SELECT AVG(age) FROM students;
Next, we will create a SELECT statement that returns the age difference between each student and a new student Tony who is aged 18 years.
SELECT name, age, ABS(age-18) AS age_difference_against_Tony FROM students;