SQL Part 6
In this blog post, I will be talking about the aggregate functions available in SQL. The first of which is the COUNT() function.
COUNT is an aggregate function in SQL that lets you count the rows in a particular column.
The above SQL statement simply counts all the rows that are available in my ramen rating database. You can see from the output that there are 2580 total rows in my dataset.
You can also count a specific row by writing
If there are NULL values in the columns, the COUNT functions would not aggregate them and instead return a value that is lower than the total numbers of rows in the dataset. Since ramens without ratings are given the string value of ‘unrated’, the star columns are considered to have no NULL value.
Another simple function is the SUM function in SQL. This function of course adds up the values in a column and returns the total. Unlike the COUNT function, the SUM function only works on numerical data.
WHERE country = ‘japan’
The above query returns the number of stars received by ramens from Japan.
MIN and MAX are functions that let you find the lowest or highest values in a column. They can be used on numerical, dates, and also non-numerical values. MIN for example returns the earliest date for dates and for string values, it returns the value that is closest to “A” as possible. Max on the other hand does the opposite. It returns the highest number, the last date and the string value closest to “Z”.
The above query returns the value of “A” Series of Artificial Chicken. The reason for this is because SQL treats as “ as a lower value than alphabets or numerical values. You can see this in the output from the query below.
ORDER BY variety
The next aggregate function is AVG, which calculates the average of a selected group of values. It can only be used on numerical values and also completely ignored NULL values which can be both a good and bad thing. You just have to be aware of whether you want 0s in place of the NULL values before you use the function.
GROUP BY 1
ORDER BY 3 DESC
The above query returns the average rating of ramen grouped by country. The number of ramen from each country is also shown to give an idea of the sample size.