Here’s another interesting problem that I solved. This one relates to SQL Server.

The problem:

To write a single database query that would allow me to get multiple row counts depending on certain pre-defined conditions. Let me make it clear … I had a table called nodes and I needed to count how many times certain non-unique records had been saved. Since the table was huge in size, the idea of using multiple queries scared me so using a single query to solve the problem was the way I found to optimize the performance and the algorithm.

The solution:

Many developers are used to write statements like select count(*) from certaintable where tablecolumn = specialcondition … that works great when you just need to count one thing at a time. My solution was to approach the problem by moving the where condition to the select section of the statement.

The query:

select
sum(case when node_id < 300 then 1 else 0 end),
sum(case when node_id > 200 then 1 else 0 end),
sum(case when node_id between 200 and 300 then 1 else 0 end)
from node

The recipe:

The secret sauce was to use the sum/case combo instead of the standard count function. By testing each condition I wanted with a case statement, and adding up the number of times each condition turned out to be true (using the sum function), I was able to achieve my goal.