المجاميع ومراحل التقرير

SELECT customer_id, COUNT(*) AS records,
 COUNT(amount) AS known_amounts, SUM(amount) AS total
FROM (VALUES (1, 10::numeric), (1, 15::numeric),
             (2, NULL::numeric)) AS p(customer_id, amount)
GROUP BY customer_id ORDER BY customer_id;

customer_id | records | known_amounts | total
1           | 2       | 2             | 25
2           | 1       | 0             | NULL

المجموع NULL نتيجة مجهولة مقصودة وليس صفراً مختلقاً.

SELECT COUNT(*) FILTER (WHERE state = 'paid') AS paid_count,
 COUNT(*) FILTER (WHERE state = 'pending') AS pending_count,
 SUM(amount) FILTER (WHERE state = 'paid') AS paid_total
FROM (VALUES ('paid', 10), ('paid', 20), ('pending', 5))
 AS p(state, amount);

paid_count | pending_count | paid_total
2          | 1             | 30

تُشتق المقاييس الثلاثة من سجلات الإدخال نفسها.