Sum in CockroachDB

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

Introduction

In this lesson, we will learn how to Sum in CockroachDB to find the total value of a set of numeric values in a Cockroach database, including:

  • What does the SUM function do in Cockroach when supplied with a group of numbers?
  • How do we best use the Sum aggregate function in our CockroachDB SQL scripts?
  • When would we make use of the Sum function in our code? We’ll practice with a realistic business use case. During our lesson, we will also look at and use CockroachDBs “GROUP BY” and “HAVING” clauses.

What is SUM?

The CockroachDB SUM function gives us the total of a set of number values. For this reason it is called an aggregate database function.

How to use SUM in SQL?

Syntax of SUM

1
intSum = SUM(group_of_values);

Analysis: intSum will be set to a value returned that is a total of the group_of_values divided by the number of values in the set represented by group_of_values.

When is SUM used?

Example of SUM

Our first test dataset is a table called tblProgLanguages for tracking some statistics for a fictional company.

idLanguagetxtNameintNumberWhoGrok
1Python16
2Java11
3C#13
4JavaScript14

The intNumberWhoGrok column in tblProgLanguages represents the number of employees or contractors in your company who know this language. We want to get a total of all the people in the company who know these four languages. This may not be a thing you want to know unless you place some constraint on the data, like what language (by adding the HAVING statement), but this initial exercise does not have to be fully realistic.

Note for the coming SQL example below. This is where we use SQL GROUP BY, which we cover in more depth in other articles. Think of it like this: If you want to total more than one value (duh), you need to look at a group of values, since there is no point to retrieving the sum of merely one value.

We will now write a Cockroach SQL script to use the SUM function to retrieve the sum we want from our recordset:

1
2
3
4
SELECT
SUM(intNumberWhoGrok) AS intSumOfNumberWhoGrok
FROM tblProgLanguages
GROUP BY ntNumberWhoGrok;

Analysis: How does the SQL script we just wrote work? We’ll go step by step:

  • (a) SELECT: We are relaying to the CockroachDB SQL interpretor that we want a total of all values (because there is no HAVING clause here) in the intNumberWhoGrok column.
  • (b) FROM: We specify the tblProgLanguages data table to get our values from.
  • (c) GROUP BY: Tells CockroachDB that we are grouping. As mentioned above, the aggregate SUM function REQUIRES a grouping of numeric values.

The above query returns the integer, “54” as the sum total and as a temp column named “intSumOfNumberWhoGrok” using the AS clause.

Now that you understand how SUM works, we will go full on realistic so you can get a deeper understanding of the CockroachDB SUM function.

Business example of Sum

Problem to solve: We had contractors and employees assess (rate) the technologies used at our company and want to know the sum of all ratings for Python. Here we will explore another use of SUM() while introducing you to the “HAVING” clause. Here’s the full data set with a table name of tblTechnologies:

idtxtEmployeetxtTechnologytxtCatTechintRating
1JimMongoNoSQL12
2FrankyMongoNoSQL18
3BettiePostgresRDB14
4GabrialPostgresRDB16
5ThomCockroachDBRDB18
6PhillipCockroachDBRDB20
7HallieCockroachDBRDB19
8RhondaPythonLang16
9BenPythonLang21
10RobbiePythonLang19
11SaulPHPLang16

Our plan is to write a query to total the total number of ratings given for the Python language.

  • (a) Consider only the rows in the above CockroachDB data where the txtCatTech column value is equal to “Lang” (meanning a programming LANGuage) AND we also need to filter for “Python” on the txtTechnology column. This is where the HAVING clause is a must have as it filters our returned rows down to three.
  • (b) We don’t care who made the rating, so no mention of the txtEmployee column is needed.
  • (c) Get a sum of the values of those 4 rows using the “intRating” column.
  • (d) Group the appropriate columns using the GROUP BY clause.
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    txtTechnology
    , SUM(intRating) AS intSumOfRating
FROM
   tblTechnologies
GROUP BY
   txtTechnology
   , intRating;
HAVING
   txtCatTech = 'Lang'
   AND
   txtTechnology = 'Python';

Analysis of the above SQL:

  • (1) SELECT: This tells CockroachDB (a) the two columns we want returned; and (b) that we want a sum of all values (limited by the HAVING clause below) in the intRating field and to name that result as intSumOfRating.
  • (2) FROM: Set tblTechnologies as the source for our Cockroach query.
  • (3) GROUP BY: Notify Cockroach that we want to group. The SUM function, an aggregate function, will only work if we group the data that CockroachDB is adding up.
  • (4) HAVING: This tells Cockroach to only use the rows that have a value of “Lang” in the txtCatTech field and having “Python” in the txtTechnology column. If you study the data, you will probably come to realize we don’t even need to filter on the tech category column because the language column is enough. But we couldn’t help slipping in a lesson on using the AND operator.

Here is the data set that will be returned when we run that SQL above:

txtTechnologyintSumOfRating
Python56

The next step might be to now find out a SUM() of ratings for PHP at the company so you can compare overall sums of how your employees feel about the two languages. To do that, we would merely change the HAVING clause, so our query looks like:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    txtTechnology
    , SUM(intRating) AS intSumOfRating
FROM
    tblTechnologies
GROUP BY
    txtTechnology
    , intRating;
HAVING
    txtCatTech = 'Lang'
    AND
    txtTechnology = 'PHP';

Note: a more complex query could give us a comparison so that we would not have to run two queries, but that is a topic for another document. Also, you may have noticed or thought of this: In the above case, getting an average of employee ratings for a given tech would be just as – if not more – meaningful than the Sum we are seeking here.

Conclusion

In this lesson, we studied how to use the SUM function in Cockroach. We even used a realistic business example so to help you see how powerful and useful the SUM aggregate function can be when used in conjunction with the GROUP BY and HAVING SQL clauses.

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.