How to count the number of instances of each foreign-key ID in a table?

sql

join

performance

best-practices

byAlex Kataev·Sep 14, 2024

To tally foreign-key occurrences swiftly, use GROUP BY with COUNT(*). Here's a well-brewed SQL potion to give you the desired counts for each key:

SELECT foreign_key_id, COUNT(*) AS total
FROM table_name  -- Don't forget to replace me with your actual table name
GROUP BY foreign_key_id; -- ...and me with your actual foreign_key_id

With this spell, you yield a count by distinct ID.

Counting tactics: Dodge null values

Counting occurrences of each ID is commonplace, especially in data normalization. To ensure no ghosts (NULL values) mess with our count, use COUNT(column_name) over COUNT(*):

SELECT foreign_key_id, COUNT(specific_column) AS total
FROM table_name 
WHERE foreign_key_id IS NOT NULL  -- We don't count ghosts here
GROUP BY foreign_key_id;

No more spooking around. Ghosts are banished!

Include the 'zero' heroes

Ensure the audience includes even those that are quite reserved (with zero counts). For this, perform a LEFT JOIN and use COALESCE to turn NULLs into zeros:

SELECT master_table.id, COALESCE(COUNT(details_table.foreign_key_id), 0) AS total
FROM master_table   -- The table of masters, not master table.
LEFT JOIN details_table ON master_table.id = details_table.foreign_key_id
GROUP BY master_table.id;

Now, everyone’s voice counts, even the silent ones.

Keep performance in check

Performance is key! With large datasets, the speed of execution can slow down as F1 car on a rush-hour traffic! Ensure your foreign_key_id columns are indexed:

CREATE INDEX idx_foreign_key ON table_name(foreign_key_id);  -- Storing keys, but not under the doormat!

This simple spell can help you win the performance Grand Prix!

Advanced counting strategies

Using subqueries

Some battles are more complex. Let's prepare with subqueries when we need to filter before counting:

SELECT foreign_key_id, 
(SELECT COUNT(*) FROM details_table WHERE details_table.foreign_key_id = main_table.foreign_key_id)
AS total
FROM main_table   -- Not your dinner table
GROUP BY foreign_key_id;