Avg in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this document, we will show how to use Avg in CockroachDB to calculate the average of a group of numeric values with SQL, including:

  • What does the AVG function do in Cockroach when given numbers?
  • How do we utilize this function in our CockroachDB SQL commands?
  • When would we use the AVG SQL function? We use and experiment with a real world use case. Much of our study here will depend on the use of the GROUP BY clause.

Prerequisites

  • Beginner-level experience with writing some SQL for Cockroach (or other RDBS like PostgreSQL, MS SQL Server, Oracle, and PstGr.) using a database administration tool for CockroachDB or one like the free Dbeaver application that adapts to be useful with many different database systems. Or by server-side coding or scripting languages like Python, PHP, Ruby, Java, C#, ASP.Net, VB.Net, or Node that provide a connection to CockroachDB, as well as a method for sending the database SQL queries, to return data or make changes to tables in your database.

What is the CockroachDB AVG function?

The CockroachDB AVG function returns the average of a group or set of numeric values. Because of it operating on groups, it is called an “aggregate function”.

AVG syntax

The syntax of the AVG function is straightforward, requiring only one parameter:

Syntax sql
intAverage = AVG(group_of_numeric_values);

Analysis:

The above SQL, when given a group of numeric values, will sum up those values and then divide the sum by the number of values. The answer will be returned, in this case, as intAverage.

Why and when to use AVG

A simple example of AVG

Let us start off with a simple example. Our initial dataset is a table we named tblLanguages.

idTechnologiestxtNameintAge
0Python9
1Java8
2ASP.Net10
3JavaScript9

Notice that the technology ages above are not necessarily true or accurate. We chose these numbers so it would be easier for you to average them in your head, like: The “8” in our second row, averaged with the “10” in our third row are easy to average in your head to “9”.

Your mission, should you choose to accept it, and which we are going to do for you – hopefully after you have done it yourself – is to use SQL to find the average age of all four technology ages in tblLanguages.

AVG in SQL

Look at the example below. Here is where we bring our knowledge of SQL GROUPing into play, if you have any. You may think of the situation this way: If you want to average more than one numeric value, you need to look at the group of numbers.

Let’s now write the SQL script to use the CockroachDB AVG function to retrieve the average we want from the table:

1
2
3
4
5
6
SELECT
    AVG(intAge)
FROM
    tblLanguages
GROUP BY
    intAge;

Analysis. How does the above work? We’ll look at all parts:

  • SELECT: We are instructing Cockroach SQL engine that we want an average of all numeric values (because we did not include a HAVING or WHERE clause) in the intAge column.
  • FROM: Specifying the tblLanguages table to retrieve data from.
  • GROUP BY: Tells CockroachDB that we are grouping. The AVG function requires grouping of the data because it is an aggregate function.

When we execute the above query, it returns an integer value of “9”.

Example of how to Average with SQL

Your mission: We had contractors and employees rate (grade) the technologies utilized at their corporation and we want to know the average of all those ratings. Here we will look at another relatively simple use of AVG while introducing the HAVING aggregate clause. Following is the data with a table name of “tblTechnologies”:

idtxtEmployeetxtTechnologytxtCatTechintGrade
1JimmyMongoNoSQL2
2FrankMongoNoSQL8
3BettyPstGrRDB4
4GabePstGrRDB6
5TomCockroachDBRDB8
6PhilCockroachDBRDB10
7HalCockroachDBRDB9
8WandaPythonLanguage7
9KenPythonLanguage11
10RobertPHPLanguage5
11PaulPHPLanguage7

Planning: The query we want to design will need to accomplish a few tasks, including:

  • (a) First, only consider the rows in our data set where the txtCatTech column value is equal to a string with the value of “Language”. This is where the HAVING clause comes into play. This narrows the scope down to 4 rows.
  • (b) Next, return the value in the txtTechnology column for those 4 rows because we want to know which technology each grade goes with, even though we won’t really be looking at individual grades. You will see. This also means we will explicitly not be caring which employee did the rating, so no mention is made in this query of the txtEmployee column.
  • (c) Get an average of the values in those four rows using the intGrade column and the AVG function.
  • (d) Use GROUP BY to group the appropriate columns for our aggregation to work.
1
2
3
4
5
6
7
8
9
10
SELECT
    txtTechnology
    , AVG(intGrade) AS intGrade_avg_returned
FROM
    technologies
GROUP BY
    txtTechnology
    , intGrade;
HAVING
    txtCatTech = 'Language'

Detailed analysis of the SQL above:

  • (a) SELECT: We are telling Cockroach that we want an average of the numeric values (respecting the HAVE statement below) from the intGrade column and to name that result as intGrade_avg_returned.
  • (b) FROM: Specify the tblTechnologies CockroachDB table as the source for our data.
  • (c) GROUP BY: Notify CockroachDB that we are grouping. The AVG function requires that we group the values that Cockroach is summing up and dividing by number of rows used.
  • (d) HAVING: This is where we instruct CockroachDB to consider only the rows that have a value of “Language” in the txtCatTech field.

Maybe you can surmise what the above CockroachDB SQL will return?

txtTechnologytxtCatTechintGrade
PythonLanguage9
PHPLanguage6

Miscellanea

You may want to study some of our other lessons that utilize the simple and useful AVG CockroachDB function as well as some that go deeper into the use of GROUP BY.

Conclusion

Here we learned how, when, and why to utilize the AVG function in CockroachDB for aggregates. We also used a realistic case example, so as to help you see the value and power of this mathematical function being used in your Cockroach SQL scripts.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.