Message-ID: <3DB710DF.DE045069@nsd.ca>
Date: Wed, 23 Oct 2002 17:13:03 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: tom@haddons.net
Cc: pgsql-sql@postgresql.org
Subject: Re: Sum of Every Column
References: <31182.66.47.66.34.1035404245.squirrel@tomandshirl.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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