What's the best way to sum multiple boolean values via SQL?
I have a table that contains, among other things, about 30 columns of boolean flags that denote certain attributes. I would like to return them, sorted by frequency, as a set of records along with the column names, for example:
Attribute Count
attrib9 43
attrib13 27
attrib19 21
etc.
My efforts so far can achieve something similar, but I can only get attributes on columns using conditional SUM, like:
SELECT SUM(IIF(a.attribIndex=-1,1,0)), SUM(IIF(a.attribWorkflow =-1,1,0))...
Also, the query is already getting a little cumbersome 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 (attribute) and are unique in the table, is it possible to use wildcards in the column names to collect all matching columns?
Also, can I modify the results to give me a sorted two-column recordset?
I am using Access 2003 and as a result the query will be through ADODB from Excel.
a source to share
It depends if you have attribute names anywhere in the data. If so, then the bird's eye view answer will do the trick. However, if the names are just column names, then you have a little more work to do and I won't do it with plain SQL.
No, you cannot use wildcards for column names in SQL. To do this, you need procedural code (i.e. a VB module in Access - you can do this in a stored procedure if you were on SQL Server). Use this code to build your SQL code.
It will be ugly. I think you'll need to do this one attribute at a time: select the row whose value is the attribute name and count-where-True, then either A) run this and store the result in a new row in the scratch table, or B) add all of these options along with the "Union" between them before running the package.
My Access VB is more than rusty, so I don't trust myself to give you something like executable code ....
a source to share
Just the score and the group have to do it
Select attribute_name
, count(*)
from attribute_table
group by attribute_name
To respond to your comment, use the analytic functions for this:
Select attribute_table.*
, count(*) over(partition by attribute_name) cnt
from attribute_table
a source to share