Aggregate Functions
These are functions used to Summarize data.
For example - The sum of all the values in a column. To perform that we use aggregate functions.
But why use them in the first place ?
Well there can be multiple situations where we need things like the total number of entries in a table, rather than counting each entry we can use aggregate functions. If we want to find total of values in a column, rather than manually adding up each value we use aggregate functions. And so many use cases !
🔢 COUNT()
This function is used to "count" number of rows in a table or count number of number of all values in a column.
Basically this function answers the "how many ?" !!
SELECT COUNT(*) FROM customers;
This returns the total count of rows in the table.
As usual * means All !
Lets see a few more examples...
SELECT COUNT(age) FROM customers;
The above query returns the number of value in the age column. If there is a NULL value it skips it.
Now a thing to be noted that it skips NULL values, so '' (empty strings) are actually counted.
We can also mix filters.
SELECT COUNT(*) FROM customers WHERE age > 25;
This returns the number of entries with age greater than 25 !
One thing to keep in mind is that doing COUNT(*) will return all the rows of a table even with NULL values or '' empty strings. Doing COUNT(column_name) will skip NULL values but still count '' empty strings.
🎐 The DISTINCT Clause
This clause is used in queries to remove duplicates. For example, There can be multiple records with same value for a column. Like same age for 2 customers or users.
Using DISTINCT removes this duplicate value when data is displayed.
SELECT DISTINCT age FROM customers;
This ensures that one age value is shown only one time. It does not affect what is stored in the table.
We can even use it with aggregate functions !
SELECT COUNT(DISTINCT age) FROM customers WHERE age > 25;
This ensures that the returned number of entries have all the ages distinct and unique, also above 25 because of the filter.
➕ SUM()
The SUM() function does exactly what it sounds, really...It just adds up stuff and spits out the result. 😐
SELECT SUM(age) FROM customers WHERE age < 500;
This just returns the sum of the values of age column, also ignores values where age is above 500. ("Kratos" is above 500 😛) !
That's basically it !
Although I found something interesting ! So we can perform basic arithmetic in SQL (+, -, *, /). But what if we try to do arithmetic on incompatible types and then spit out there sum ?
Let's see
SELECT SUM(age * name) FROM customers;
And turns out it returns -> 0. YES 0 ! I expected an error. Although this behavior depends on DBMS to DBMS. Some convert incompatible value types to 0 or NULL, some throw error.
🥑 AVG()
The average function. Same as SUM() does exactly what it sounds like. It returns average of values of a column.
SELECT AVG(age) FROM customers;
Returns the average of ages of all the customers !
And average means -> Sum of all the values / Number of values !
In SQL Language something like -> SUM(<column>) / COUNT(<column>) ! I don't think SQL implements it like this but its an analogy.
And as expected trying to do arithmetic operations on incompatible data types will cause 0.0, or error in other DB.
But what if we do something like this:
SELECT AVG(id * age) FROM customers;
That takes id and age of each row and multiply them and then proceed as intended. The denominator does not changes and remains the same i.e. number of rows.
📉 MIN()
The Minimum function returns the minimum of all the records in a column.
SELECT MIN(age) FROM customers;
This will return the record which has the lowest age.
📈 MAX()
The Maximum function, does exactly the opposite of what MIN() did.
It returns the maximum of all the records in a column.
SELECT MAX(age) FROM customers;
This returns the highest record in the column.
In short, Aggregate functions help us quickly summarize data across rows, instead of checking each row manually.
That was aggregate functions.
Follow GROUP BY to continue from here.
Browser Full SQL Page.