Aggregate functions

Examples

The following examples use a sample orders table:

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    amount DECIMAL(10, 2),
    status TEXT
);

INSERT INTO orders VALUES
    (1, 101, 250.00, 'completed'),
    (2, 101, 80.50,  'completed'),
    (3, 102, NULL,   'pending'),
    (4, 103, 430.00, 'completed'),
    (5, 102, 120.75, 'cancelled');

Count rows and non-null values:

-- Total number of rows
SELECT count(*) FROM orders;
-- Result: 5

-- Number of rows where amount is not null
SELECT count(amount) FROM orders;
-- Result: 4

Compute aggregates on non-null values:

SELECT
    avg(amount)    AS average_amount,   -- ignores the NULL row
    sum(amount)    AS total_amount,
    max(amount)    AS max_amount,
    min(amount)    AS min_amount
FROM orders;
-- average_amount: 220.3125
-- total_amount:   881.25
-- max_amount:     430.00
-- min_amount:     80.50

Group results and collect values into an array:

SELECT
    customer_id,
    array_agg(amount ORDER BY amount) AS amounts
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_id;
-- customer_id 101: {80.50, 250.00}
-- customer_id 103: {430.00}

Concatenate values into a string:

SELECT string_agg(status, ', ' ORDER BY order_id) AS statuses
FROM orders;
-- Result: 'completed, completed, pending, completed, cancelled'

Examples

The following examples use a sample measurements table with sensor readings:

CREATE TABLE measurements (
    sensor_id INT,
    reading DOUBLE PRECISION
);

INSERT INTO measurements VALUES
    (1, 10.5),
    (1, 12.3),
    (1, NULL),
    (2, 8.7),
    (2, 9.1),
    (2, 11.4);

Compute standard deviation and variance per sensor:

SELECT
    sensor_id,
    stddev_samp(reading)  AS sample_stddev,
    stddev_pop(reading)   AS pop_stddev,
    var_samp(reading)     AS sample_variance,
    var_pop(reading)      AS pop_variance
FROM measurements
GROUP BY sensor_id;

Fit a simple linear regression between two columns:

SELECT
    corr(reading, sensor_id)           AS correlation,
    regr_slope(reading, sensor_id)     AS slope,
    regr_intercept(reading, sensor_id) AS intercept,
    regr_r2(reading, sensor_id)        AS r_squared
FROM measurements;