Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: tom@haddons.net, <pgsql-sql@postgresql.org>
Subject: Re: Sum of Every Column
Date: Wed, 23 Oct 2002 14:15:31 -0700
References: <31182.66.47.66.34.1035404245.squirrel@tomandshirl.com>
In-Reply-To: <31182.66.47.66.34.1035404245.squirrel@tomandshirl.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210231415.31926.josh@agliodbs.com>

 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 yo=
u=20
design it yourself?

>=20
> SELECT breast_cancer_resources.*
> FROM breast_cancer_resources, agency_contact_info
> WHERE breast_cancer_resources.id=3Dagency_contact_info.id
> 	AND agency_contact_info.guideregion=3D1
>             AND agency_contact_info.list_online=3DTRUE
>=20
> 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=20
procedural language to loop through the columns and create sum clauses for=
=20
each one.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco