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...