content/posts/postgres-aggregates/aggregation.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20


EXPLAIN (
  ANALYZE,
  COSTS,
  VERBOSE,
  BUFFERS
)
SELECT
  products.id AS product_id,
  products.name AS product_name,
  COUNT(DISTINCT reviews.id) AS num_reviews,
  COUNT(DISTINCT orders.id) AS num_orders
FROM
  products
  LEFT JOIN reviews ON reviews.product_id = products.id
  LEFT JOIN orders ON orders.product_id = products.id
  -- WHERE
  --   products.name ilike '%0'
GROUP BY
  products.id;