How to count number of associated records, when association has composite primary key?

user_20816 0
Viewed 92

Given a customers table, and an associated orders table, I want to count the number of orders made per customer.

However, the orders table has a composite primary key. Here are the schemas and the test data:

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);

There are two customers: Joe (with 3 orders) and Jane (with no orders).

This query...

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

...correctly counts that Joe has 3 orders, but incorrectly counts that Jane has 1 order (she actually has 0).

That's because COUNT((orders.section_id, orders.item_id)) produces 1. Even though section_id and item_id are both NULL, the tuple expression (NULL, NULL) is not considered NULL.

How do I properly query the orders count in the face of composite primary keys?

postgresql sql
2 Answers

I'm not sure why do you want to count on both fields: section_id and item_id...

I'd suggest to provide simple experiment:

SELECT (NULL, NULL) result
-- produces: (,)

So, when you use id with COUNT, it have to return 1 - as it's NOT NULL.

SELECT COALESCE(NULL, NULL) result
-- produces: NULL

So, when you use id with COUNT, it have to return 0 - as it's NULL.

db<>fiddle

As to me, it's enough to use COUNT(item_id) or COUNT(section_id), as both can NOT be null. So...

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;

Unless... you want to get distinct sections/items in each order of specific customer.

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;

db<>fiddle

user_21420280

Count orders.customer_name, that's the one that's guaranteed not to be NULL if there was a join partner row as it's used in the ON clause with =.

SELECT customers.*,
       count(orders.customer_name) AS num_orders
       FROM customers
            LEFT JOIN orders
                      ON customers.name = orders.customer_name
       GROUP BY customers.name;
user_96614240
Related