From: Jim Buttafuoco <jim@contactbda.com>
To: James Moliere <jmoliere@ucsd.edu>
To: pgsql-sql@postgresql.org
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> (raw)
In-Reply-To: <005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org>
References: <005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org>
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 -------
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: jim@contactbda.com, jmoliere@ucsd.edu
Subject: Re: create aggregate function 'count_bool( column_name, boolean )'
In-Reply-To: <20060707145216.M76744@contactbda.com>
* 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