Thread: Sum of Every Column

Hi Folks,

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):

SELECT breast_cancer_resources.*
FROM breast_cancer_resources, agency_contact_info
WHERE breast_cancer_resources.id=agency_contact_info.idAND 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)?

Thanks, Tom

Tom,

You can add 
sum( case when <boolean_field> then 1 else 0 end) 
for each field that you need.

JLL

Tom Haddon wrote:
> 
> Hi Folks,
> 
> 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):
> 
> 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)?
> 
> Thanks, Tom
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

 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 BerkusAglio Database SolutionsSan Francisco

Hi tom.

In postgresql you cannot have functions with
a variable number of parameters.
(pgsql supports some kind of method overloading
based on the type and number of parameters,
thats the reason why).

But if you run Unix (with freebsd and linux it is trivial
as you will see, with solaris you have to be more formal)
there is a hack.

I had the same problem as yours, but in my case
i wanted the minimum, maximum of double numbers (maximum 14 of them).

In BSD,linux you can navigate the process' stack with no problems.
The idea is to write a variable parameter number function,
with the first parameter denoting the number of the rest
parameters, (just like printf)
and then define a pgsql function with the maximum number
of parameters that you will ever have.

I attach the code (tested under RedHat 7.1, kernel 2.4.7,
glibc-2.2.2-10,gcc-2.96-81 and FreeBSD 4.6.1-RC2, both with
postgresql 7.2.1),



==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr