ABS() and AVG() function in SQLite
1. ABS() Function :
ABS(X) is a math function used to get the absolute value of a number.
Syntax –
ABS(X)
The ABS(X) function takes a numeric argument X and returns its magnitude.
SELECT ABS(-6);
Output –
6
If the argument X is a blob or string, the function gives 0.0 as the return value
SELECT ABS('gfg');
Output –
0.0
If the argument X is NULL the function returns NULL.
SELECT ABS(NULL);
Output –
NULL
2. AVG() Function :
AVG(X) is an aggregate function used to calculate the average on a set of rows.
Syntax –
AVG(X)
The function yields a floating point value equal to the average of all NON-NULL values in the argument group X.
SELECT AVG(1,2,3,4,5,6,7)
Output –
4.0
If there is a NULL value passed to the AVG() function, only the non-NULL values are used in calculating the average.
SELECT AVG(10,20,30,NULL);
Output –
20.0
Strings and blobs present in the argument X are evaluated as 0.
SELECT AVG(10,20,'word',30,);
Output –
20.0
The function returns a NULL if all the input values are NULL.
SELECT AVG(NULL,NULL,NULL);
Output –
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;
Output –
20.25
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;
Output –
Output of a SELECT statement using ABS() function
Last Updated :
08 Oct, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...