Grouping​

Learn how to gain aggregated insights on your data with the GROUP BY statement

What is a GROUP BY statement?

GROUP BY is a standard SQL statement used for grouping records together by a common value. This can be for example grouping all orders made by individual customers. Since Firestore is a NoSQL database, it doesn’t provide native support for grouping. However, with Jetspike you can utilize this feature to gain detailed insights into your data.

Example data

Imagine we have following table with all orders our customers have made:

As the list can grow longer, it would be useful to see aggregated data for individual customers instead of the complete list of orders. Following SQL command gives us the aggregated list:

SELECT customerId, SUM(price) AS totalPrice, COUNT(_documentId) AS numOfOrders
FROM orders
GROUP BY customerId

The command will return following results:

Aggregate functions

Instead one price, we have now multiple prices for each customer. To view the values, we have to use one of the aggregate functions.

COUNT() – the number of returned rows

SUM() – the total sum of all returned numeric values

AVG() – the average of a set of values

MIN() – the lowest value of a set of values

MAX() – the highest value of a set of values

Advanced use cases

In the following example, we want to see how our business did in individual months. Since we don’t have a dedicated field for the month number in our database, we first have to extract it from the date in a subselect an then use it for grouping.

SELECT month, SUM(price) AS totalPrice, COUNT(_documentId) AS numOfOrders
FROM (SELECT _documentId, price, DATEPART('m', date) AS month FROM orders)
GROUP BY month
ORDER BY month

The command will return following results:

Grouping by multiple fields

Jetspike currently doesn’t allow grouping by multiple fields. You use a work-around with builing a custom grouping key in a subselect if necessary.

Why doesn't Firestore support grouping by itself?

Firestore is a NoSQL database, unlike traditional relational databases like SQL. NoSQL databases have excellent performance and scalability, but this comes at the costs of limited features. Aggregate operations have to be performed in the application layer. Jetspike gives you additional capabilities on top of Firestore for fast and efficient data analytics without the need to build a client application connecting to Firestore.

Further reading

You are now armed with useful knowledge of aggregating data with the GROUP BY statement. Here are more articles to help you with your data analytics tasks with Jetspike.