content/posts/postgres-aggregates/subqueries.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


EXPLAIN (
  ANALYZE,
  COSTS,
  VERBOSE,
  BUFFERS
)
SELECT
  products.id,
  products.name,
(
    SELECT
      COUNT(id)
    FROM
      reviews
    WHERE
      product_id = products.id) AS num_reviews,
(
    SELECT
      COUNT(id)
    FROM
      orders
    WHERE
      product_id = products.id) AS num_orders
FROM
  products;

-- WHERE
--   products.id % 10 = 0;