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 ; 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 ; 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 ; 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 ; 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 ; 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 ; Fri, 7 Jul 2006 06:53:46 -0700 (PDT) From: "James Moliere" To: 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

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 =3D true;
select count(was_selected) from = some_table where was_selected =3D true;

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


Any help would be = appreciated.

Thanks!

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