pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSum of Every Column
5+ messages / 4 participants
[nested] [flat]
* Sum of Every Column
@ 2002-10-23 20:17 Tom Haddon <tom@haddons.net>
2002-10-23 21:13 ` Re: Sum of Every Column Jean-Luc Lachance <jllachan@nsd.ca>
2002-10-23 21:15 ` Re: Sum of Every Column Josh Berkus <josh@agliodbs.com>
2002-10-24 07:36 ` Re: Sum of Every Column Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 3 replies; 5+ messages in thread
From: Tom Haddon @ 2002-10-23 20:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sum of Every Column
2002-10-23 20:17 Sum of Every Column Tom Haddon <tom@haddons.net>
@ 2002-10-23 21:13 ` Jean-Luc Lachance <jllachan@nsd.ca>
2 siblings, 0 replies; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-10-23 21:13 UTC (permalink / raw)
To: tom@haddons.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sum of Every Column
2002-10-23 20:17 Sum of Every Column Tom Haddon <tom@haddons.net>
@ 2002-10-23 21:15 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-10-23 21:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sum of Every Column
2002-10-23 20:17 Sum of Every Column Tom Haddon <tom@haddons.net>
@ 2002-10-24 07:36 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2002-10-24 07:36 UTC (permalink / raw)
To: Tom Haddon <tom@haddons.net>; +Cc: pgsql-sql
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
Attachments:
mini_maxi.c (text/plain, 1018B) download
#include <pgsql/include/server/postgres.h>
#include <stdio.h>
int minihack(argc)
int argc;
{
int i;
int temp = *(&argc + 1);
int tval;
for (i=1;i<argc;i++) {
tval = *(&argc+1+i);
if (tval < temp && tval !=0)
temp = tval;
}
return temp;
}
int4 mini(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14)
int4 t1;
int4 t2;
int4 t3;
int4 t4;
int4 t5;
int4 t6;
int4 t7;
int4 t8;
int4 t9;
int4 t10;
int4 t11;
int4 t12;
int4 t13;
int4 t14;
{
return minihack(14,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14);
}
int maxihack(argc)
int argc;
{
int i;
int temp = *(&argc + 1);
int tval;
for (i=1;i<argc;i++) {
tval = *(&argc+1+i);
if (tval > temp)
temp = tval;
}
return temp;
}
int4 maxi(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14)
int4 t1;
int4 t2;
int4 t3;
int4 t4;
int4 t5;
int4 t6;
int4 t7;
int4 t8;
int4 t9;
int4 t10;
int4 t11;
int4 t12;
int4 t13;
int4 t14;
{
return maxihack(14,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14);
}
* Sum of Every Column
@ 2002-10-23 20:17 Tom Haddon <tom@haddons.net>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Haddon @ 2002-10-23 20:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-10-24 07:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-23 20:17 Sum of Every Column Tom Haddon <tom@haddons.net>
2002-10-23 21:13 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-10-23 21:15 ` Josh Berkus <josh@agliodbs.com>
2002-10-24 07:36 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-10-23 20:17 Sum of Every Column Tom Haddon <tom@haddons.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox