By Ben Nadel on March 15, 2007
Daniel Roberts, Tony Petruzzi. and Rick Osborne just taught me some revolutionary stuff in SQL. Unbeknownst to me, SQL aggregates (ex. SUM, COUNT) can handle CASE statements as part of their execution logic. This is HUGE! I can't believe I didn't know this (or rather, I can easily believe it, but I am saddened).
So just quickly, as this post is more about excitement than it is about teaching, let me just show you how I might rework my previous SQL with new CASE statements:
Just want to point out that per record functions can be quite taxing for large queries versus other solutions.
I worked on a reporting site that originally ran from a log table in an Access database which was
later moved to MSSQL. There was summing on t/f fields to come up with some counts and since Access t/f values are -1 the absolute value was found for summing. It looked like this: Sum(Abs(field)). Notice anything? Well the functions are improperly nested for this need. Just switching around the nesting to Abs(Sum(field)) greatly improvement execution time. That is 1 Abs() call versus N records of calls.
Queries on a well indexed database may perform better using subqueries to get counts and such versus aggregates on case statements. Just one more thing to think about :-)
You can actually make it simpler. because you are just counting, you can use a 1 instead of the field for the value to count, and if you want a null, you can just leave out the else: