From jmoliere@ucsd.edu Fri Jul  7 14:13:07 2006
X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 787F79FB0D1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 11:13:07 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 03615-03-3 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 11:13:00 -0300 (ADT)
X-Greylist: delayed 00:18:57.591221 by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id B66169FB1FB
	for <pgsql-sql@postgresql.org>; Fri,  7 Jul 2006 11:12:52 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from mailbox8.ucsd.edu (mailbox8.ucsd.edu [132.239.1.60])
	by svr4.postgresql.org (Postfix) with ESMTP id AA81B5AF08B
	for <pgsql-sql@postgresql.org>; Fri,  7 Jul 2006 13:53:53 +0000 (GMT)
Received: from smtp.ucsd.edu (smtp.ucsd.edu [132.239.1.49])
	by mailbox8.ucsd.edu (8.13.6/8.13.5) with ESMTP id k67DrkSb039657
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK)
	for <pgsql-sql@postgresql.org>; Fri, 7 Jul 2006 06:53:47 -0700 (PDT)
Received: from Moliere (cpe-66-74-215-130.san.res.rr.com [66.74.215.130])
	by smtp.ucsd.edu (8.13.6/8.13.4) with ESMTP id k67DrjgS014016
	for <pgsql-sql@postgresql.org>; Fri, 7 Jul 2006 06:53:46 -0700 (PDT)
From: "James Moliere" <jmoliere@ucsd.edu>
To: <pgsql-sql@postgresql.org>
Subject: create aggregate function 'count_bool( column_name, boolean )'
Date: Fri, 7 Jul 2006 06:53:45 -0700
Message-ID: <005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_005C_01C6A192.17DF11B0"
X-Mailer: Microsoft Office Outlook 11
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
Thread-Index: AcahzMOPNzk3vfPZShKJpeg5p0l77w==
X-Greylisting: NO DELAY (Trusted relay host);
	processed by UCSD_GL-v2.1 on mailbox8.ucsd.edu;
	Fri, 07 July 2006 06:53:47 -0700 (PDT)
X-MailScanner: PASSED (v1.2.8 38380 k67DrkSb039657 mailbox8.ucsd.edu)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/100
X-Sequence-Number: 25674

This is a multi-part message in MIME format.

------=_NextPart_000_005C_01C6A192.17DF11B0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello,
I'd like to create a function called count_bool( column_name, boolean ) in
PostgreSQL.

this function is similar to the count( column_name ) function but will only
count the trues or falses based on the other input parameter boolean. e.g.
if you pass in a 'true', all the trues will be counted but not the falses --
it's the same but opposite if the 'false' was passed in.



I'd like this aggregate function to be developed with the SQL language
instead of 'C' (for maintenance reasons).  From the surface, it appears to
be an incredibly simple job only to find that it's difficult. 


In some respects, I can't believe this function doesn't exist in SQL

so now I can create the statement
select distict product_id, count_bool(purchased, true),
count_bool(was_selected, true) from some_table group by product_id;

instead of breaking the query into 3 seperate queries

select distict product_id from some_table;
select count(purchased) from product_id where purchased = true;
select count(was_selected) from some_table where was_selected = true;



Am I missing a detail with SQL based aggregate function development?


Any help would be appreciated.

Thanks!


------=_NextPart_000_005C_01C6A192.17DF11B0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
tt
	{font-family:"Courier New";}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><tt><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:
10.0pt'>Hello,</span></font></tt><font size=3D2 face=3D"Courier =
New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'><br>
<tt><font face=3D"Courier New">I'd like to create a function called =
count_bool(
column_name, boolean ) in PostgreSQL.</font></tt><br>
<br>
<tt><font face=3D"Courier New">this function is similar to the count( =
column_name
) function but will only count the trues or falses based on the other =
input parameter
boolean. e.g. if you pass in a 'true', all the trues will be counted but =
not
the falses -- it's the same but opposite if the 'false' was passed =
in.</font></tt><br>
<br>
<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>I&#8217;d like this aggregate function to be
developed with the SQL language instead of &#8216;C&#8217; (for =
maintenance
reasons).&nbsp; <tt><font face=3D"Courier New">From the surface, it =
appears to be an
incredibly simple job only to find that it's difficult. =
<o:p></o:p></font></tt></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><br>
<tt><font face=3D"Courier New">In some respects, I can't believe this =
function
doesn't exist in SQL</font></tt><br>
<br>
<tt><font face=3D"Courier New">so now I can create the =
statement</font></tt><br>
<tt><font face=3D"Courier New">select distict product_id, =
count_bool(purchased,
true), count_bool(was_selected, true) from some_table group by =
product_id;</font></tt><br>
<br>
<tt><font face=3D"Courier New">instead of breaking the query into 3 =
seperate
queries</font></tt><br>
<br>
<tt><font face=3D"Courier New">select distict product_id from =
some_table;</font></tt><br>
<tt><font face=3D"Courier New">select count(purchased) from product_id =
where
purchased =3D true;</font></tt><br>
<tt><font face=3D"Courier New">select count(was_selected) from =
some_table where
was_selected =3D true;</font></tt><br>
<br>
<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>Am I missing a detail with SQL based =
aggregate
function development?<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><br>
<tt><font face=3D"Courier New">Any help would be =
appreciated.</font></tt><br>
<br>
<tt><font face=3D"Courier New">Thanks!</font></tt></span></font><font =
size=3D2
face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial'><o:p></o:p></span></font></p=
>

</div>

</body>

</html>

------=_NextPart_000_005C_01C6A192.17DF11B0--




From jim@contactbda.com Fri Jul  7 14:54:21 2006
X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 3773A9FB1EB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 11:54:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 14242-01-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 11:54:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from amanda.contactbda.com (ipn36372-e65122.cidr.lightship.net
	[216.204.66.226])
	by postgresql.org (Postfix) with ESMTP id 647B29FB201
	for <pgsql-sql@postgresql.org>; Fri,  7 Jul 2006 11:54:10 -0300 (ADT)
Received: from amanda.contactbda.com (amanda.contactbda.com [192.168.1.2])
	by amanda.contactbda.com (8.12.11/8.12.11/Debian-3) with ESMTP id
	k67Es7PR019910; Fri, 7 Jul 2006 10:54:07 -0400
From: "Jim Buttafuoco" <jim@contactbda.com>
To: "James Moliere" <jmoliere@ucsd.edu>, <pgsql-sql@postgresql.org>
Reply-To: jim@contactbda.com
Subject: Re: create aggregate function 'count_bool( column_name, boolean )'
Date: Fri, 7 Jul 2006 10:54:07 -0400
Message-Id: <20060707145216.M76744@contactbda.com>
In-Reply-To: <005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org>
References: <005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org>
X-Mailer: Open WebMail 2.41 20040926
X-OriginatingIP: 192.168.1.1 (jim)
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/101
X-Sequence-Number: 25675

James,

I know Postgresql doesn't have 2 arg aggregate functions.  what you could do is the following (untested)

select distict product_id, 
sum(case when purchased then 1 else 0 end) as purchased,
sum(case when was_selected then 1 else 0 end) as was_selected
from some_table 
group by product_id;

Jim

---------- Original Message -----------
From: "James Moliere" <jmoliere@ucsd.edu>
To: <pgsql-sql@postgresql.org>
Sent: Fri, 7 Jul 2006 06:53:45 -0700
Subject: [SQL] create aggregate function 'count_bool( column_name, boolean )'

> Hello,
> I'd like to create a function called count_bool( column_name, boolean ) in
> PostgreSQL.
> 
> this function is similar to the count( column_name ) function but will only
> count the trues or falses based on the other input parameter boolean. e.g.
> if you pass in a 'true', all the trues will be counted but not the falses --
> it's the same but opposite if the 'false' was passed in.
> 
> I'd like this aggregate function to be developed with the SQL language
> instead of 'C' (for maintenance reasons).  From the surface, it appears to
> be an incredibly simple job only to find that it's difficult.
> 
> In some respects, I can't believe this function doesn't exist in SQL
> 
> so now I can create the statement
> select distict product_id, count_bool(purchased, true),
> count_bool(was_selected, true) from some_table group by product_id;
> 
> instead of breaking the query into 3 seperate queries
> 
> select distict product_id from some_table;
> select count(purchased) from product_id where purchased = true;
> select count(was_selected) from some_table where was_selected = true;
> 
> Am I missing a detail with SQL based aggregate function development?
> 
> Any help would be appreciated.
> 
> Thanks!
------- End of Original Message -------




From rabroersma@yahoo.com Fri Jul  7 15:59:59 2006
X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 6B8179FA322
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 12:59:59 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 70261-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 12:59:56 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from web31806.mail.mud.yahoo.com (web31806.mail.mud.yahoo.com
	[68.142.207.69]) by postgresql.org (Postfix) with SMTP id 0818D9FA0EC
	for <pgsql-sql@postgresql.org>; Fri,  7 Jul 2006 12:59:55 -0300 (ADT)
Received: (qmail 93211 invoked by uid 60001); 7 Jul 2006 15:59:24 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=DCB3jJVUc/rPOunvZTOXTad3o9r247gzqJnraOfgBqfz+ellYUt0E3JYkMg08um+qle779Brtb6NhK5EiL11B/+p3uyaW3dEZ4DKh1AbIfQjyxhhwdBKQcGrVJxRnUE3W7WrtMQEAOlHBI4DkXBEl1Iz35W2JiFIpu2RqYZVnOA=
	;
Message-ID: <20060707155924.93209.qmail@web31806.mail.mud.yahoo.com>
Received: from [65.211.203.97] by web31806.mail.mud.yahoo.com via HTTP;
	Fri, 07 Jul 2006 08:59:24 PDT
Date: Fri, 7 Jul 2006 08:59:24 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: create aggregate function 'count_bool( column_name, boolean )'
To: James Moliere <jmoliere@ucsd.edu>, pgsql-sql@postgresql.org
In-Reply-To: <005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/104
X-Sequence-Number: 25678

> so now I can create the statement
> select distict product_id, count_bool(purchased, true),
> count_bool(was_selected, true) from some_table group by product_id;
> 
> instead of breaking the query into 3 seperate queries
> 
> select distict product_id from some_table;
> select count(purchased) from product_id where purchased = true;
> select count(was_selected) from some_table where was_selected = true;
> Am I missing a detail with SQL based aggregate function development?
> Any help would be appreciated.

how about:

select 
    product_id, 
    (
      select count(purchased)
      from some_table as A2
      where purchased=true and A1.product_id=A2.product_id
    ) as TP,
    (
      select count(selected)
      from some_table as A3
      where purchased=true and A1.product_id=A3.product_id
    ) as TS
from
  some_table as A1
group by
  product_id;

Regards,

Richard Broersma Jr.