The SQL Count Query Trick

Whenever you think you know everything about SQL you will soon find out that you don't. That's what I experienced recently when learning how you can turn any query, of any complexity, into a count-query that gives you just the number of records the original query would deliver.

In real life you need a count-query for pagination, i.e. for finding out how many database records are available in total. Only by means of such a count you can calculate the number of pages that a user can select from. The problem arises when the query is a huge join that may return a different result when you simply replace the attributes inside the select clause by a count(*). Besides, you don't want to duplicate a big complex query into a count-sister, do you?

So how to do this? It is a trick. But it works.

→ You make your big complex query a sub-select, that means you enclose it in parentheses. Then you prepend a select count(*) from, and append an arbitrary alias name, like e.g. X:

select count(*) from (
  -- any complex query goes here
) X    -- the alias used to count

Assuming you have the following query ....

  select e.name, c.name 
  from employee e inner join company c on c.employee_id = e.id 
  where c.deleted is null and c.state_id in
    (select s.id from state s where s.reachable = 1)

.... and you want to know the total count of its possible result.
Then your count query looks like this (don't forget the trailing alias, it is needed):

select count(*) from (
  select e.name, c.name
  from employee e inner join company c on c.employee_id = e.id
  where c.deleted is null and c.state_id in
    (select s.id from state s where s.reachable = 1)
) X

This is now counting eggs instead of delivering records :-)