Aggregate

Summarizes many rows into one row.

When applied:

• without group, it produces one row from the whole table,
• within a group pipeline, it produces one row from each group.

1. aggregate [{expression or assign operations}]

Note

Currently, all declared aggregation functions are min, max, count, average, stddev, avg, sum and count_distinct. We are in the process of filling out std lib.

Examples

PRQL

1. from employees
2. aggregate [
3. average salary,
4. ct = count
5. ]

SQL

1. SELECT
2. AVG(salary),
3. COUNT(*) AS ct
4. FROM
5. employees

PRQL

1. from employees
2. group [title, country] (
3. aggregate [
4. average salary,
5. ct = count
6. ]
7. )

SQL

1. SELECT
2. title,
3. country,
4. AVG(salary),
5. COUNT(*) AS ct
6. FROM
7. employees
8. GROUP BY
9. title,
10. country

Aggregate is required

Unlike in SQL, using an aggregation function in derive or select (or any other transform except aggregate) will not trigger aggregation. By default, PRQL will interpret such attempts functions as window functions:

PRQL

1. from employees
2. derive [avg_sal = average salary]

SQL

1. SELECT
2. *,
3. AVG(salary) OVER () AS avg_sal
4. FROM
5. employees

This ensures that derive does not manipulate the number of rows, but only ever adds a column. For more information, see window transform.