Learn SQL COUNT, AVG and SUM Functions

Often covered in a web coding bootcamp, SQL COUNT, AVG and SUM functions will serve as the jumping point for more advanced data analysis using SQL. 

Functions to Aggregate Data

The SQL COUNT, AVG and SUM functions share the common feature of aggregating data, which makes them incredibly useful for working with both vast amounts and smaller quantities of data. 

Keep in mind that of the following aggregate functions, the only one that includes NULL values is the SQL COUNT function. 

SQL COUNT

The first and simplest of these aggregate functions to learn is SQL COUNT, designed to count the number of lines within your table including NULL values. 

Assuming you have a basic base function, such as: 

You would make the change from * to “COUNT(column_name)”. You can also get away with excluding the column name when using SQL COUNT and instead just leave it as a *, which would look like:

The result of this command will give you the total number of lines within your table. 

SQL SUM

Using the SQL SUM function will add all of the values within a given column together to give you their sum. While you do not have to specify your column when using SQL COUNT, you do with SQL SUM. Here, the code would be: 

This particular function can save significant time, as it allows you to conduct basic arithmetic within the code itself without exporting to another spreadsheet program. 

By its nature, the SUM function will ignore NULL values. 

SQL AVG

SQL AVG finds the average value in a given column. It looks like this:

Using the SQL AVG function will ignore any NULL values, as they would skew the results otherwise. 

SQL MAX and MIN

A web coding bootcamp will likely also cover maximums and minimums. Both the SQL MIN and SQL MAX functions work just like the functions mentioned above, so this is a simple command to use once you have the hang of the others. 

To find the minimum, use:

To find the maximum, use:

Remember that both MIN and MAX ignore NULL values, as do the other aggregate functions except for SQL COUNT. 

Learn to Adjust Basic Calculations

Once you learn SQL COUNT, AVG, SUM, MIN and MAX, you should understand how to adjust those commands to get even more information. 

GROUP BY

The GROUP BY clause lets you customize any of the above aggregation functions, allowing you to divide each calculation based on factors, such as the value in a different column. 

To use this function, specify the columns that you plan to use as input, including both the column you will use for segmentation and the one whose values you want to evaluate. Next, specify which column or columns create the segmentation. When done in conjunction with SQL COUNT or another function, it will automatically create groups based on your chosen column and then sort the data lines appropriately. After this, it will be able to complete your calculations. 

Continuing the above example with SQL COUNT, it would look like: 

WHERE

You can also incorporate WHERE into your SQL functions, just like you would in any other setting. In this case, you would place the line of code for the WHERE constraint immediately after the “FROM table” line. 

ORDER BY

To further help organize the results of your SQL COUNT, you can sort the columns by using ORDER BY. 

This goes at the end of the code and is fairly straightforward. It would look something like:

ORDER BY is followed by “count.” This is a very important consideration since the column returned by your SQL COUNT is a new one that needs a name, which will be “count” as the default. As such, your new reference to it will have to use the SQL default name. 

Using ORDER BY on its own will automatically give you the list in ascending order. If you prefer descending order, add the keyword DESC after. It would look like:

DISTINCT

You can also complement your knowledge from aggregate functions with DISTINCT, which will only deliver unique values. 

When you use DISTINCT, you remove any duplicates and end up with a list of the unique values within a given column. The code would look like:

You could also get a similar result by using GROUP BY to let your function create groups based on the various values of the column. This would look like:

Using DISTINCT is much simpler than using this method.

Keeping your code as simple as possible is always a bonus, as it saves you time, makes it easier to incorporate future expansions and makes it simpler to find any errors.

A Note About Order and Practice

As you look at the above examples of aggregate SQL functions, you will notice that the order of the code is not necessarily intuitive. Sometimes, the final line of code has a significant impact on the first or second line. Since this can be counterintuitive for beginners, it is one of the many reasons to practice coding functions like SQL COUNT before working on more advanced functions and clauses. This ensures you fully understand the use of clauses and sorting before your coding becomes more complex. 

Conclusion

There are five aggregate functions used in SQL. COUNT is considered one of the most basic and counts the number of lines; SQL AVG determines the average; SQL SUM determines the sum; SQL MIN determines the minimum value; and SQL MAX determines the maximum value. Most web coding bootcamps should introduce each of these functions, as well as ways to sort such as ORDER BY and GROUP BY. With a clear understanding of each of these functions, you should be able to incorporate the basic aggregate functions into your SQL code.

*Please note, these articles are for educational purposes and the topics covered may not be representative of the curriculum covered in our boot camp. Explore our curriculum to see what you’ll learn in our program.