SQL Reporting Aggregates: Conditions, Completeness, and Weights

Build reporting aggregates without losing the denominator

This PostgreSQL 16 practice guide goes beyond the function definitions. It builds conditional metrics, detects incomplete amounts, combines grouped results correctly, and handles ties in a report. Every example is self-contained and read-only. If you need COUNT versus AVG fundamentals first, use the core aggregate lesson.

Define the order contract

The six-row source contains one current row per order ID. A paid order can have zero amount, but NULL means its amount is unknown. Cancelled orders should not contribute to paid revenue. All known amounts use the same currency and are finite. The report population is the regions present in this complete fixture; adding regions with no rows requires a separate region dimension.

Compute several conditional measures at one grain

WITH orders(order_id,region,status,amount) AS (
 VALUES(1,'A','paid',10::numeric),(2,'A','paid',20),(3,'A','cancelled',100),
 (4,'B','paid',0),(5,'B','paid',NULL),(6,'B','cancelled',40)
)
SELECT region,COUNT(*) AS all_orders,
 COUNT(*) FILTER(WHERE status='paid') AS paid_orders,
 COUNT(amount) FILTER(WHERE status='paid') AS paid_known_amounts,
 SUM(amount) FILTER(WHERE status='paid') AS known_paid_amount,
 CASE WHEN COUNT(*) FILTER(WHERE status='paid')=
           COUNT(amount) FILTER(WHERE status='paid')
      THEN COALESCE(SUM(amount) FILTER(WHERE status='paid'),0) END AS complete_paid_amount
FROM orders GROUP BY region ORDER BY region;

region | all_orders | paid_orders | paid_known_amounts | known_paid_amount | complete_paid_amount
A | 3 | 2 | 2 | 30 | 30
B | 3 | 2 | 1 | 0 | NULL

Region A has two paid orders totaling 30. Region B also has two paid orders, but one amount is unknown. Its known subtotal is zero; its complete paid total is not established. Reporting only COALESCE(SUM(amount), 0) would conceal that distinction. The explicit equality of paid row count and paid known-amount count makes completeness reviewable.

Understand conditional COUNT and SUM

FILTER is PostgreSQL syntax supported by the target version. A conditional CASE can express many equivalent aggregates in other dialects. COUNT(CASE WHEN condition THEN 1 END) counts only matching rows because unmatched expressions are NULL. Adding ELSE 0 to that COUNT would count the unmatched rows too. By contrast, SUM(CASE WHEN condition THEN 1 ELSE 0 END) sums flags.

WITH orders(order_id,region,status,amount) AS (
 VALUES(1,'A','paid',10::numeric),(2,'A','paid',20),(3,'A','cancelled',100),
 (4,'B','paid',0),(5,'B','paid',NULL),(6,'B','cancelled',40)
)
SELECT COUNT(CASE WHEN status='paid' THEN 1 END) AS paid_count,
 COUNT(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS wrong_paid_count,
 SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_flag_sum
FROM orders;

paid_count | wrong_paid_count | paid_flag_sum
4 | 6 | 4

The six in the middle is an intentionally wrong paid-order count. A cancelled row contributes a non-NULL zero, which COUNT still counts. Be explicit about what the aggregate consumes rather than memorizing a CASE template.

Combine summaries using their original weights

WITH daily(region,known_count,amount_sum) AS (
 VALUES('A',1,100::numeric),('B',9,0),('C',0,0)
)
SELECT ROUND(SUM(amount_sum)/NULLIF(SUM(known_count),0),2) AS observation_average,
 ROUND(AVG(amount_sum/NULLIF(known_count,0)),2) AS unweighted_region_average
FROM daily;

observation_average | unweighted_region_average
10.00 | 50.00

One observation at 100 and nine at zero have an observation average of 10. Averaging region A’s mean of 100 and B’s mean of zero gives 50; that is an equal-region-weight question. Region C has no observations and no defined mean. Carry sums and counts when rolling up summaries, and keep a separate completeness policy for unknown values.

Use a second stage for rankings and aggregate-of-aggregate questions

WITH orders(order_id,day) AS (
 VALUES(1,DATE '2026-01-01'),(2,DATE '2026-01-01'),
 (3,DATE '2026-01-02'),(4,DATE '2026-01-02'),(5,DATE '2026-01-03')
), daily AS (
 SELECT day,COUNT(*) AS orders FROM orders GROUP BY day
), ranked AS (
 SELECT *,DENSE_RANK() OVER(ORDER BY orders DESC) AS rnk FROM daily
)
SELECT day,orders FROM ranked WHERE rnk=1 ORDER BY day;

day | orders
2026-01-01 | 2
2026-01-02 | 2

Both days tie for the most orders. ORDER BY count DESC LIMIT 1 would choose only one, and without another ordering key it would not define which one. If the requirement is exactly one winner, specify the tie rule. A separate grouped stage also makes questions such as “average orders per observed day” explicit; including days with no orders requires a calendar spine and changes the denominator.

Select rows and groups at different stages

WITH orders(order_id,region,status,amount) AS (
 VALUES(1,'A','paid',10::numeric),(2,'A','paid',20),(3,'A','cancelled',100),
 (4,'B','paid',0),(5,'B','paid',NULL),(6,'B','cancelled',40)
)
SELECT region,COUNT(*) AS known_paid_orders,SUM(amount) AS amount
FROM orders WHERE status='paid' AND amount IS NOT NULL
GROUP BY region HAVING COUNT(*)>=2 ORDER BY region;

region | known_paid_orders | amount
A | 2 | 30

This question selects regions with at least two paid orders whose amounts are known. It is not “at least two paid orders of any completeness,” which would include B. Moving the amount filter or changing the count expression changes the contract even if the query continues to run. Use a named intermediate result when that improves reviewability.

Reconcile the report and test failures

The original fixture has six orders, four paid orders, three known paid amounts, and known paid amount 30. A correct report should reconcile those counts without pretending the complete paid amount is known. Add an empty input, an all-NULL region, a repeated order version, and a zero denominator before adopting the query. For repeated versions, choose the authoritative version before calculating eligibility and totals.

Indexes, partitioning, and summary tables solve different performance problems. Do not create an index and declare the query faster without a representative plan and measurement. Cached or materialized summaries also need a refresh and late-arrival policy. For a direct comparison of grouped and row-preserving results, see GROUP BY versus PARTITION BY.

FAQ

Does FILTER remove the group? It restricts one aggregate’s input; another aggregate can use other rows in the same group. Is a NULL complete total a failure? It honestly reports missing required amounts. Can I nest AVG(SUM(...)) directly? Ordinary aggregates at the same query level need separate stages; an aggregate used as a window expression is a different construct. References: aggregates and window functions.