Home About



May 2024

April 2024

March 2024

February 2024

January 2024

December 2023

November 2023

October 2023

September 2023

August 2023

July 2023

June 2023

May 2023

April 2023

March 2023

February 2023

January 2023

December 2022

back to home

The badass guide to data handling - Using SQL to aggregate and join Peakboard Hub List data

14 November 2023 • 4 mins

In today’s article, we will learn how to use SQL statements with functions for aggregation and table join, in order to extract data from Peakboard Hub tables.

We’ve also covered various topics related to Peakboard Hub lists in other articles (check out the links at the bottom of this page).

Why would you want to use aggregation / joins when you can use data flows for data manipulation?

The most obvious reason is to limit network traffic. Let’s say you have 10k+ rows of sensor data, and you want to get the number of rows. Then it doesn’t make sense to download all 10k+ rows just to count them. Instead, you can let Peakboard Hub do the counting, and just download one row, which contains the result.

The same is true with table joins: Let’s assume you have orders and order lines. The order has a date column. But you want to see the order lines of the order on a specific date. Of course, you could download all orders and all order lines, join them, and throw away all order lines that do not belong to orders on your desired date. Or, you can just use a table join to solve this problem on the source side, to avoid transferring too much data over the network.

Aggregate data by using aggregate functions

Let’s take a look at an example of how we can aggregate data by using aggregate functions.

The basis of this example is a simple table of sensor data. Specifically, it’s a list of temperature values. We have two columns:

  • TS - the date and time when the temperature was taken
  • Temperature - the actual temperature


Let’s assume we are not interested in all the data. The only thing we want to know (and present on our dashboard later) is the maximum and the minimum temperature that was recorded today. To do that, we can give this SQL statement to the Hub:

min(temperature) as MinTemperature, 
max(temperature) as MaxTemperature
from temperaturedata
where cast(TS as date) = cast(getdate() as date)

To use direct, plain SQL in the Peakboard Hub List data source, you need to activate the SQL mode by clicking on the “Select with SQL” button:


Then, you can use the above statement and check your result on the right side. Now, you have condensed thousands of rows down to the two numbers you’re interested in:


Joining Peakboard Hub lists

Now, let’s take a look at how we can join two Peakboard Hub lists.

In this example, we have a very simple relational connection between two tables:

  • “Products” contains the products we have in stock. It has a column for the product number, and a column for the quantity of that product in the warehouse.

  • “ProductTexts” contains a brief product description for each product number, in different languages (English, German, and Chinese).

Here’s the “Products” table:


And here’s the “ProductTexts” table:


In our Peakboard application, we want a single table that contains our current stock quantities for each product and the German description texts. We can do that with this SQL statement:

select p.ProductNo, Quantity, Producttext
Products as p inner join ProductTexts as pt
on p.ProductNo = pt.ProductNo
where Language = 'DE'

and here is the result:



Preparing data in the source before bringing them into the Peakboard application is a wise choice, because it increases performance and limits network bandwidth. Beside Peakboard Hub lists (as shown in this article), this works with almost any database or SQL-based environment. You should consider this option every time you design a new data source for a Peakboard project.

The badass guide to data handling - Using SQL to aggregate and join Peakboard Hub List data


Want to get notified when a new article is published? Sign up below!