Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE customers (
    name TEXT NOT NULL PRIMARY KEY
);
INSERT INTO customers VALUES ('Joe');
INSERT INTO customers VALUES ('Jane');

CREATE TABLE orders (
    customer_name TEXT NOT NULL REFERENCES customers (name),
    section_id INT NOT NULL,
    item_id INT NOT NULL,
    PRIMARY KEY (customer_name, section_id, item_id)
);
INSERT INTO orders VALUES ('Joe', 1, 100);
INSERT INTO orders VALUES ('Joe', 1, 101);
INSERT INTO orders VALUES ('Joe', 2, 110);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT c.*,
  COUNT(o.section_id) AS num_orders
FROM customers c
LEFT JOIN orders o
  ON c.name = o.customer_name
GROUP BY c.name;


name
num_orders


Jane
0

Joe
3


SELECT c.*,
  COUNT(DISTINCT o.section_id) AS num_sections,
  COUNT(DISTINCT o.item_id) AS num_items
FROM customers c
LEFT JOIN orders o
  ON c.name = o.customer_name
GROUP BY c.name;


name
num_sections
num_items


Jane
0
0

Joe
2
3