Returning several COUNT results from one ASP SQL statement

Question

Say I have a table like this:

Field1  Field2  Field3  Field4
fred    tom     fred    harry 
tom             tom
dick    harry
harry           

and I want to determine what proportion of it has been completed for each field.

I can execute:

SELECT COUNT (Field1) WHERE (Field1 <> '') AS Field1Count      
SELECT COUNT (Field2) WHERE (Field2 <> '') AS Field2Count
SELECT COUNT (Field3) WHERE (Field3 <> '') AS Field3Count      
SELECT COUNT (Field4) WHERE (Field4 <> '') AS Field4Count  

Is it possible to roll up these separate SQL statements into one that will return the 4 results in one hit? Is there any performance advantage to doing so (given that the number of columns and rows may be quite large in practice)?

Answer 1

Here's how I would go about it using MySQL

select sum(CASE WHEN Field1 <>'' THEN 1 ELSE 0 END) as Field1Count 
     , sum(CASE WHEN Field2 <>'' THEN 1 ELSE 0 END) as Field2Count 
     , sum(CASE WHEN Field3 <>'' THEN 1 ELSE 0 END) as Field3Count 
     ...
     , sum(CASE WHEN FieldN <>'' THEN 1 ELSE 0 END as FieldNCount 
  from DataTable

Answer 2

You can do like this:

select
  sum(case when Field1 <> '' then 1 else 0 end) as Field1Count,
  sum(case when Field2 <> '' then 1 else 0 end) as Field2Count,
  sum(case when Field3 <> '' then 1 else 0 end) as Field3Count,
  sum(case when Field4 <> '' then 1 else 0 end) as Field4Count
from TheTable

Answer 3

If you set your unpopulated fields to be NULL instead of blanks, you could rely on the fact that count() will not include NULL fields. All solutions with per-row function (if, case, coalesce and so on) are fine for small databases but will not scale well to big databases. Keep in mind that small is a relative term, it might still be okay for your databases even if you think they're big - I work in a shop where millions of rows are the sizes of our configuration tables :-)

Then you can just use:

select
    count(field1) as count1,
    count(field2) as count2,
    count(field3) as count3,
    count(field4) as count4
from ...

(or count(distinct fieldX) for distinct values, of course).

If that's a plausible way to go, you can just get your table set up with:

update tbl set field1 = NULL where field1 = '';
update tbl set field2 = NULL where field2 = '';
update tbl set field3 = NULL where field3 = '';
update tbl set field4 = NULL where field4 = '';

But, as with all database performance questions, measure, don't guess. And measure in the target environment (or suitable copy). And measure often. Database tuning is not a set-and-forget operation.