SELECT, Filtering & Aggregates

PostgreSQL fully supports standard SQL query syntax and adds several powerful extensions. This chapter covers SELECT through HAVING, highlighting the PostgreSQL-specific features that make it uniquely expressive.

We'll use this sample dataset:

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer    TEXT,
    region      TEXT,
    product     TEXT,
    quantity    INTEGER,
    unit_price  NUMERIC(10,2),
    order_date  DATE
);

INSERT INTO orders (customer, region, product, quantity, unit_price, order_date) VALUES
    ('Alice',   'North', 'Widget',  3, 19.99, '2024-01-05'),
    ('Bob',     'South', 'Gadget',  1, 49.99, '2024-01-07'),
    ('Carol',   'North', 'Widget',  5, 19.99, '2024-01-10'),
    ('Alice',   'East',  'Gadget',  2, 49.99, '2024-01-12'),
    ('Dave',    'South', 'Widget',  4, 19.99, '2024-01-15'),
    ('Carol',   'East',  'Doohick', 1, 99.99, '2024-01-18'),
    ('Bob',     'North', 'Widget',  2, 19.99, '2024-01-20'),
    ('Alice',   'North', 'Doohick', 1, 99.99, '2024-01-22');

SELECT and FROM

-- Select all columns
SELECT * FROM orders;

-- Select specific columns with expressions
SELECT
    order_id,
    customer,
    quantity * unit_price  AS total_value,
    UPPER(product)         AS product_upper
FROM orders;

WHERE — Filtering Rows

-- Basic comparison
SELECT * FROM orders WHERE region = 'North';

-- Multiple conditions
SELECT * FROM orders
WHERE region = 'North'
  AND unit_price > 20.00;

-- IN list
SELECT * FROM orders
WHERE region IN ('North', 'East');

-- BETWEEN (inclusive on both ends)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-10' AND '2024-01-20';

-- LIKE pattern matching
SELECT * FROM orders WHERE product LIKE 'G%';    -- starts with G
SELECT * FROM orders WHERE product ILIKE 'g%';   -- case-insensitive (PostgreSQL-specific)

-- IS NULL / IS NOT NULL
SELECT * FROM orders WHERE region IS NOT NULL;

DISTINCT and DISTINCT ON

Standard DISTINCT removes duplicate rows across all selected columns:

-- Unique regions
SELECT DISTINCT region FROM orders ORDER BY region;

DISTINCT ON is a powerful PostgreSQL extension that keeps only one row per group of a specified column — you choose which row by using ORDER BY:

-- Get the most recent order per customer
SELECT DISTINCT ON (customer)
    customer,
    order_id,
    order_date,
    product
FROM orders
ORDER BY customer, order_date DESC;

GROUP BY and Aggregates

-- Count orders per region
SELECT
    region,
    COUNT(*)              AS order_count,
    SUM(quantity)         AS total_units,
    AVG(unit_price)       AS avg_price,
    MIN(order_date)       AS first_order,
    MAX(order_date)       AS last_order
FROM orders
GROUP BY region
ORDER BY total_units DESC;

ARRAY_AGG and STRING_AGG

PostgreSQL aggregates can collect values into arrays or strings:

-- Collect all products ordered per customer into an array
SELECT
    customer,
    ARRAY_AGG(product ORDER BY order_date)  AS products_ordered,
    STRING_AGG(product, ', ' ORDER BY order_date) AS products_csv
FROM orders
GROUP BY customer;

JSON_AGG

-- Aggregate rows into a JSON array
SELECT
    customer,
    JSON_AGG(JSON_BUILD_OBJECT('product', product, 'qty', quantity)) AS order_details
FROM orders
GROUP BY customer;

The FILTER Clause — PostgreSQL-Specific

The FILTER clause allows conditional aggregation without CASE expressions. It is far cleaner than the traditional workaround:

-- Traditional approach (works everywhere but verbose)
SELECT
    region,
    COUNT(CASE WHEN product = 'Widget' THEN 1 END)  AS widget_orders,
    COUNT(CASE WHEN product = 'Gadget' THEN 1 END)  AS gadget_orders
FROM orders
GROUP BY region;

-- PostgreSQL FILTER clause (clean and expressive)
SELECT
    region,
    COUNT(*) FILTER (WHERE product = 'Widget')  AS widget_orders,
    COUNT(*) FILTER (WHERE product = 'Gadget')  AS gadget_orders,
    SUM(quantity * unit_price) FILTER (WHERE unit_price > 50) AS premium_revenue
FROM orders
GROUP BY region;

HAVING — Filtering Groups

HAVING filters groups after GROUP BY (the way WHERE filters individual rows before grouping):

-- Only regions with more than 2 orders
SELECT
    region,
    COUNT(*) AS order_count
FROM orders
GROUP BY region
HAVING COUNT(*) > 2;

-- Customers who spent more than $100 total
SELECT
    customer,
    SUM(quantity * unit_price) AS total_spend
FROM orders
GROUP BY customer
HAVING SUM(quantity * unit_price) > 100
ORDER BY total_spend DESC;

ROLLUP, CUBE, and GROUPING SETS

These are extensions for multi-level subtotals — very useful for reporting.

ROLLUP — Hierarchical Subtotals

-- Sales totals at each level: region+product, region, grand total
SELECT
    region,
    product,
    SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY ROLLUP (region, product)
ORDER BY region NULLS LAST, product NULLS LAST;

The NULL rows are subtotals — NULL region is the grand total, NULL product is the region subtotal.

CUBE — All Combinations

-- CUBE generates subtotals for every combination
SELECT
    region,
    product,
    SUM(quantity) AS units
FROM orders
GROUP BY CUBE (region, product);
-- Generates: (region, product), (region), (product), ()

GROUPING SETS — Custom Combinations

-- Exactly the groupings you want
SELECT
    region,
    product,
    SUM(quantity) AS units
FROM orders
GROUP BY GROUPING SETS (
    (region, product),   -- detail level
    (region),            -- region subtotal
    ()                   -- grand total
);

Use GROUPING(col) to tell NULL-subtotals apart from actual NULLs:

SELECT
    CASE GROUPING(region) WHEN 1 THEN 'ALL REGIONS' ELSE region END AS region,
    SUM(quantity) AS units
FROM orders
GROUP BY ROLLUP (region);

ORDER BY and LIMIT

-- Sort by computed column
SELECT customer, SUM(quantity * unit_price) AS spend
FROM orders
GROUP BY customer
ORDER BY spend DESC
LIMIT 3;

-- FETCH FIRST — SQL-standard alternative to LIMIT
SELECT customer, SUM(quantity * unit_price) AS spend
FROM orders
GROUP BY customer
ORDER BY spend DESC
FETCH FIRST 3 ROWS ONLY;

-- Pagination: skip first 3, show next 3
SELECT * FROM orders ORDER BY order_id LIMIT 3 OFFSET 3;