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> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jmoliere@ucsd.edu
Subject: Re: create aggregate function 'count_bool( column_name, boolean )'
In-Reply-To: <005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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