SQL Part 6

Aggregate Functions

In this blog post, I will be talking about the aggregate functions available in SQL. The first of which is the COUNT() function.

COUNT()

COUNT is an aggregate function in SQL that lets you count the rows in a particular column.

SELECT COUNT(*)
   FROM ramenratings

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

SELECT COUNT(stars)
   FROM ramenratings

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.

SUM()

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.

SELECT SUM(stars)
   FROM ramenratings
   WHERE country = ‘japan’

The above query returns the number of stars received by ramens from Japan.

MIN/MAX

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”.

SELECT MIN(variety)
   FROM ramenratings

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.

SELECT *
   FROM ramenratings
   ORDER BY variety

AVG

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.

SELECT country,
     COUNT(*),
     AVG(stars)
     FROM ramenratings
     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.