How best to sum multiple boolean values via SQL?

Question

I have a table that contains, among other things, about 30 columns of boolean flags that denote particular attributes. I'd like to return them, sorted by frequency, as a recordset along with their column names, like so:

Attribute    Count
attrib9      43
attrib13     27 
attrib19     21
etc.

My efforts thus far can achieve something similar, but I can only get the attributes in columns using conditional SUMs, like this:

SELECT SUM(IIF(a.attribIndex=-1,1,0)), SUM(IIF(a.attribWorkflow =-1,1,0))...

Plus, the query is already getting a bit unwieldy with all 30 SUM/IIFs and won't handle any changes in the number of attributes without manual intervention.

The first six characters of the attribute columns are the same (attrib) and unique in the table, is it possible to use wildcards in column names to pick up all the applicable columns?

Also, can I pivot the results to give me a sorted two-column recordset?

I'm using Access 2003 and the query will eventually be via ADODB from Excel.

Answer 1

Just a simple count and group by should do it

  Select attribute_name
         , count(*)
      from attribute_table
    group by attribute_name

To answer your comment use Analytic Functions for that:

 Select attribute_table.*
        , count(*) over(partition by attribute_name) cnt
   from attribute_table

Answer 2

This depends on whether or not you have the attribute names anywhere in data. If you do, then birdlips' answer will do the trick. However, if the names are only column names, you've got a bit more work to do--and I'm afriad you can't do it with simple SQL.

No, you can't use wildcards to column names in SQL. You'll need procedural code to do this (i.e., a VB Module in Access--you could do it within a Stored Procedure if you were on SQL Server). Use this code build the SQL code.

It won't be pretty. I think you'll need to do it one attribute at a time: select a string whose value is that attribute name and the count-where-True, then either A) run that and store the result in a new row in a scratch table, or B) append all those selects together with "Union" between them before running the batch.

My Access VB is more than a bit rusty, so I don't trust myself to give you anything like executable code....

Answer 3

In Access, Cross Tab queries (the traditional tool for transposing datasets) need at least 3 numeric/date fields to work. However since the output is to Excel, have you considered just outputting the data to a hidden sheet then using a pivot table?