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.