total sum grand total aggregate function

Introducing aggregate functions
Nande Konst, Monday, October 15, 2018

In the latest version of the Jexia Javascript SDK you can now use aggregate functions in your application. They can help you to perform a calculation of a set of values.

When are aggregate functions useful?

There are cases when you don’t want to provide detailed information but just figures. Aggregate functions provide us an easy way to show summarized data from a dataset. They are extremely useful for reporting:

  • Most sold products
  • Least sold products
  • All the products in the dataset
  • Total turnover
  • Average amount of $ spend

All aggregate functions are deterministic and always return the same value each time they are called.

Using aggregate functions in Jexia

You can use the following aggregate functions with the .field operator:

  • MAX
  • MIN
  • AVG
  • SUM
  • COUNT

You can use them like this:

{ fn: , col: }

All the examples are made in NodeJS.

COUNT Function:

The COUNT function returns the total number of values in a specified field:

jexiaSDK.jexiaClient().init(credentials, dataModule);
dataModule
.dataset("products")
.select()
.fields({fn: "COUNT", col:"*"})
.execute()
.then(records => console.log(records))
.catch(error => console.error(error));

The output shows that are 3 records in the dataset.

records: [ { 'COUNT(id)': 3 } ]

For COUNT function you can provide the asterisk (*) as a field name. Also you can combine field names with aggregation functions to get more complicated results:

[..]
.fields("product_name","price",{fn: "COUNT", col:"*"})
[..]
records: [ 
  { product_name: null, price: 45.5, 'COUNT(id)': 1 },
  { product_name: null, price: 20.5, 'COUNT(id)': 1 },
  { product_name: null, price: 15.5, 'COUNT(id)': 1 }
]

MIN Function:

The MIN function returns the smallest value in the specified dataset field. Let’s show the product with the lowest price:

[..]
.fields({fn: "MIN", col:"price"})
[..]

In the console we can see that the dataset entry with the lowest price value has been retrieved:

record: [ { ‘MIN(price)’: 15.5 } ]

MAX Function:

The MAX function is the opposite of the MIN function. It returns the largest value from the specified dataset field:

.fields({fn: "MAX", col:"price"})

The output in the console shows us the dataset entry with the largest price value:
records: [ { ‘MAX(price)’: 45.5 } ]

SUM Function

The SUM function returns the sum of all values in the specified column. Sum works on numeric fields only. Null values are not allowed.

AVG Function

The AVG function returns the average of the values in a specified dataset field. Like the SUM function, the AVG function can only be applied on numeric data types.

Tags: , ,

Categorised in:

This post was written by Nande Konst

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.