SQL BASICS: Aggregate Functions
Hello! Welcome to day 10 of my #100daysofcode challenge. Today, we are going to talk about aggregate functions!
Aggregates are calculations performed on multiple rows of a table. Today,we are going to be working on our cats database again and we are going to be using the following aggregates:
- COUNT(): counts the number of rows
- SUM() calculates the sum of the values in a column
- MAX()/MIN(): the largest/smallest value
- AVG(): calculates the average of the values in a column
- ROUND(): rounds the values in the column
COUNT
COUNT() is a function that takes the name of a column and counts the number of values that are not NULL in that column.
If we wanted to count all the rows in our cats table, we would write this:
SUM
SUM() takes the name of a column as an arguments and returns the sum of all the values in said column.
Let’s say, we wanted to know the age of all the cats added together, we would write something like this:
MAX() AND MIN()
MAX() and MIN() return the highest and lowest values in a column.
How old is the oldest cat?
The oldest cat is 10! How old is the youngest cat?
The youngest cat is 0!
AVG()
AVG() returns the calculated average value of a specified column. The example below will return the average of all the ages in our cats table:
ROUND()
By default, SQL tries to be as precise as possible without rounding. To make things easier to read, we use the ROUND() function. The ROUND() function takes two parameters, a column name and an integer. If we wanted to round the price of the cats to 0 decimal places, we would write this:
It would return a list of all the rows of cats with their price rounded to the nearest 0.
GROUP BY
Most of the time, we are going to want to grab the aggregate for data with specific characteristics. For example, we might want to know the average price for cat at each age. You may think we have to do something like this:
BUT WE DON’T! We can just use GROUP BY!
GROUP BY is a clause in SQL that is used with aggregate functions. It collaborates with SELECT to arrange identical data into groups.
Remember, the GROUP BY statement comes after WHERE statements but before ORDER BY or LIMIT.
That’s it! Thanks for tuning in!
