Agregaciones y GROUP BY en PostgreSQL

Agregaciones y GROUP BY en PostgreSQL | Nicolás Garzón

SQL

SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'paid') AS paid,
  count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  sum(total_amount) FILTER (WHERE status = 'paid') AS revenue
FROM orders;

SQL

SELECT
  o.id,
  jsonb_agg(
    jsonb_build_object(
      'productId', oi.product_id,
      'quantity', oi.quantity,
      'unitPrice', oi.unit_price
    )
    ORDER BY oi.product_id
  ) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

SQL

WITH item_totals AS (
  SELECT order_id, sum(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, sum(amount) AS paid
  FROM payments
  GROUP BY order_id
)
SELECT o.id, it.total, pt.paid
FROM orders o
LEFT JOIN item_totals it ON it.order_id = o.id
LEFT JOIN payment_totals pt ON pt.order_id = o.id;

SQL

SELECT
  branch_id,
  status,
  sum(total_amount) AS total,
  GROUPING(branch_id) AS branch_is_total,
  GROUPING(status) AS status_is_total
FROM orders
GROUP BY GROUPING SETS (
  (branch_id, status),
  (branch_id),
  (status),
  ()
);

SQL

SELECT
  branch_id,
  date_trunc('day', paid_at AT TIME ZONE $1) AS local_day,
  count(*) AS orders,
  count(DISTINCT customer_id) AS customers,
  sum(total_amount) AS revenue,
  avg(total_amount) AS average_ticket
FROM orders
WHERE status = 'paid'
  AND paid_at >= $2
  AND paid_at < $3
GROUP BY branch_id, local_day
ORDER BY local_day, branch_id;