Re: Sum of Every Column

Tom,

> I'm hoping to put together a query that generates a report on a table with
> a large number of boolean fields. This report has to be able to adapt to
> the number of fields in the table. Essentially, I want it to provide the
> sum of TRUE values for each field for a given subset of the table. I've
> got the query that returns the subset of the table (this is based on a
> relationship with another table):

Sounds like you have schema problems. Is this a legacy database, or did you
design it yourself?

>
> SELECT breast_cancer_resources.*
> FROM breast_cancer_resources, agency_contact_info
> WHERE breast_cancer_resources.id=agency_contact_info.id
> AND agency_contact_info.guideregion=1
> AND agency_contact_info.list_online=TRUE
>
> But I'm not sure how to generate the sum for each column. Should I be
> looking elsewhere than SQL to do this for me, such as php (this is for a
> web-based report)?

You cannot dynamically sum all columns through SQL. You would need to use a
procedural language to loop through the columns and create sum clauses for
each one.

--
-Josh Berkus
Aglio Database Solutions
San Francisco