create aggregate function 'count_bool( column_name, boolean )' - Mailing list pgsql-sql

From James Moliere
Subject create aggregate function 'count_bool( column_name, boolean )'
Date
Msg-id 005b01c6a1cc$c43de9b0$0302a8c0@intelesis.org
Whole thread Raw
Responses Re: create aggregate function 'count_bool( column_name, boolean )'
Re: create aggregate function 'count_bool( column_name, boolean )'
List pgsql-sql
<div class="Section1"><p class="MsoNormal"><tt><font face="Courier New" size="2"><span style="font-size:
10.0pt">Hello,</span></font></tt><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""><br/><tt><font face="Courier New">I'd like to create a function called count_bool( column_name, boolean ) in
PostgreSQL.</font></tt><br/><br /><tt><font face="Courier New">this function is similar to the count( column_name )
functionbut 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 /></span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">I’d like this aggregate function to be developed with the SQL language instead of ‘C’ (for
maintenancereasons).  <tt><font face="Courier New">From the surface, it appears to be an incredibly simple job only to
findthat it's difficult. </font></tt></span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New""><br /><tt><font face="Courier New">In some respects, I can't believe this function doesn't
existin SQL</font></tt><br /><br /><tt><font face="Courier New">so now I can create the statement</font></tt><br
/><tt><fontface="Courier New">select distict product_id, count_bool(purchased, true), count_bool(was_selected, true)
fromsome_table group by product_id;</font></tt><br /><br /><tt><font face="Courier New">instead of breaking the query
into3 seperate queries</font></tt><br /><br /><tt><font face="Courier New">select distict product_id from
some_table;</font></tt><br/><tt><font face="Courier New">select count(purchased) from product_id where purchased =
true;</font></tt><br/><tt><font face="Courier New">select count(was_selected) from some_table where was_selected =
true;</font></tt><br/><br /></span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">Am I missing a detail with SQL based aggregate function development?</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New""><br /><tt><font face="Courier New">Any help would be appreciated.</font></tt><br /><br
/><tt><fontface="Courier New">Thanks!</font></tt></span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font></div>

pgsql-sql by date:

Previous
From: T E Schmitz
Date:
Subject: SELECT substring with regex
Next
From: "Jim Buttafuoco"
Date:
Subject: Re: create aggregate function 'count_bool( column_name, boolean )'