pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate aggregate function 'count_bool( column_name, boolean )'
3+ messages / 3 participants
[nested] [flat]
* create aggregate function 'count_bool( column_name, boolean )'
@ 2006-07-07 13:53 James Moliere <jmoliere@ucsd.edu>
0 siblings, 2 replies; 3+ messages in thread
From: James Moliere @ 2006-07-07 13:53 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create aggregate function 'count_bool( column_name, boolean )'
@ 2006-07-07 14:54 Jim Buttafuoco <jim@contactbda.com>
parent: James Moliere <jmoliere@ucsd.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Jim Buttafuoco @ 2006-07-07 14:54 UTC (permalink / raw)
To: James Moliere <jmoliere@ucsd.edu>; pgsql-sql
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 -------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create aggregate function 'count_bool( column_name, boolean )'
@ 2006-07-07 15:59 Richard Broersma Jr <rabroersma@yahoo.com>
parent: James Moliere <jmoliere@ucsd.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Broersma Jr @ 2006-07-07 15:59 UTC (permalink / raw)
To: James Moliere <jmoliere@ucsd.edu>; pgsql-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.
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-07-07 15:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-07 13:53 create aggregate function 'count_bool( column_name, boolean )' James Moliere <jmoliere@ucsd.edu>
2006-07-07 14:54 ` Jim Buttafuoco <jim@contactbda.com>
2006-07-07 15:59 ` Richard Broersma Jr <rabroersma@yahoo.com>
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