Aggregation and math

Performing aggregation is also a familiar operation. To write a query to count the number of breweries by state, you use the built-in count aggregate function:

SELECT state, COUNT(*) AS Count
FROM beer-sample
WHERE type = "brewery"
GROUP BY state

As you might expect, N1QL supports mathematical and aggregate operations such as AVG, ROUND, MIN, MAX, and SUM. You can use these operations to perform calculations on either aggregated data or on projected columns. As another example of aggregation with N1QL, this query computes the average abv (alcohol by volume) of a brewery's beer brands:

SELECT brewery_id, AVG(abv) AS Average
FROM beer-sample
WHERE type = "beer"
AND abv != 0 
GROUP BY brewery_id

Similarly, if you want to find the beer with the highest or lowest alcohol content, you can use the MAX or MIN function respectively. In the following snippet, the HAVING clause is added to the GROUP BY clause to filter the results:

SELECT name, MAX(abv) AS Strength
FROM beer-sample...