Aggregation, Views, and Updates: Choosing the Right Boundary

Aggregation, views, and stored routines look like separate SQL features. They share one design question: at what boundary should the database turn raw rows into a reusable result or operation?

GROUP BY changes the level of a row

Before grouping, one row may represent one order. After grouping by customer, one row represents one customer summary.

SELECT customer_id,
       COUNT(*) AS orders,
       SUM(total) AS revenue
FROM orders
WHERE created_at >= DATE '2026-01-01'
GROUP BY customer_id
HAVING SUM(total) >= 1000;

WHERE filters orders before grouping. HAVING filters customer groups afterward. Every selected expression must either identify the group or be computed from the group.

In plain terms: GROUP BY changes what one output row means. Many mistakes come from selecting a column that still belongs to the old row level.

Be precise with counts: COUNT(*) counts rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts distinct non-NULL values and usually costs more.

A view stores a query, not its result

CREATE VIEW paid_order_summary AS
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

An ordinary view is a named query. Each use expands into an underlying plan, which makes the view useful for a stable interface or shared definition—not as an automatic cache.

A materialized view stores the result and must be refreshed:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

The tradeoff is freshness versus read cost. If the dashboard tolerates data that is five minutes old, precomputation may be useful. If every payment must appear immediately, stale materialization may be unacceptable.

Make updates atomic

Avoid read-modify-write sequences that let two sessions overwrite each other:

-- Prefer one atomic statement.
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42
  AND quantity > 0
RETURNING quantity;

If no row is returned, the item was unavailable. The predicate and update happen as one database operation.

Stored routines are an interface decision

A stored procedure can keep several statements close to the data and expose one permission boundary. It can also hide logic from application tests, couple deployments to one database, and create a second codebase.

Use a routine when the operation must be atomic, is shared by several clients, or benefits materially from running beside the data. Keep orchestration and external service calls in application code.

Decision table

Need	Tool
Reusable live query	View
Expensive report with acceptable staleness	Materialized view
Safe decrement or state transition	Atomic UPDATE with a predicate
Multi-statement data operation shared by clients	Stored routine, considered carefully
Workflow involving APIs and retries	Application service

Review card

• WHERE filters rows; HAVING filters groups.
• Grouping changes the meaning of one result row.
• A view stores a definition; a materialized view stores data.
• Prefer atomic updates over application read-modify-write.
• Stored routines are useful boundaries, not default homes for business logic.

SELECT customer_id,
       COUNT(*) AS orders,
       SUM(total) AS revenue
FROM orders
WHERE created_at >= DATE '2026-01-01'
GROUP BY customer_id
HAVING SUM(total) >= 1000;

CREATE VIEW paid_order_summary AS
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42
  AND quantity > 0
RETURNING quantity;