select and calculate with postgres using subqueries



~2 min read


220 words

I wanted to be able to retrieve a set of records from a database along with specific details from a related table. I knew SQL could accommodate this use-case, but I’d never actually done it — so, today I learned.

Imagine the following situation. Two tables, my_table, and my_related_table, are related through a foreign-key relationship (whether formal or not is not relevant here). erd my table

My desired row data will be:

    id: string,
    name: string,
    has_custom: boolean,
    num_enabled: number,
    num_related: number

In this case, has_custom is derived by looking at all related records and identifying if any are labeled as is_custom. Similarly, num_enabled is the count of related records where is_enabled is true.

  EXISTS( SELECT * FROM my_related_table AS r WHERE r.lookup_id = AND is_custom = TRUE) AS has_custom,
  ( SELECT COUNT(*) FROM my_related_table AS r WHERE r.lookup_id = AND r.is_enabled = TRUE) AS num_enabled,
  ( SELECT COUNT(*) FROM my_related_table AS r WHERE r.lookup_id = AS num_related
FROM my_table AS t;

One thing to note - just as a column could be relabeled using as - so too can subquery results. Note the AS xxx following the subqueries on the my_related_table labels the columns in a more readable / communicative way versus the default exists/count.

psql query return

Hi there and thanks for reading! My name's Stephen. I live in Chicago with my wife, Kate, and dog, Finn. Want more? See about and get in touch!