Re: Drawing a blank on some SQL - Mailing list pgsql-sql

Not tested.<br /><br />1. select count(t2.activity_id),<br />       t1.activity_id<br />from (select distinct
activity_idfrom foo_activity) as t1,     -- assumes all activities exist somewhere in table<br />left join foo_activity
t2on (t1.activity_id = t2.activity_id)<br /> WHERE created >= '01/01/2011' and created < '01/08/2011'<br />group
by2<br />order by 2;<br /><br />2. -- use generate_series for simple numbering scheme<br />select
count(t2.activity_id)<br/>       t1.x as 'activity_id"<br /> from generate_series(1,12) as t1(x),     <br />left join
foo_activityt2 on (t1.x = t2.activity_id)<br />WHERE created >= '01/01/2011' and created < '01/08/2011'<br
/>groupby 2<br />order by 2;<br /><br /><div class="gmail_quote"> On Fri, Feb 11, 2011 at 1:46 PM, Aaron Burnett <span
dir="ltr"><<ahref="mailto:aburnett@bzzagent.com">aburnett@bzzagent.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><br
/>Hi,<br /><br /> I'm just drawing a blank entirely today and would appreciate some help on<br /> this.<br /><br /> The
longand short; there are 12 distinct activities that need to be queried<br /> on a weekly basis:<br /><br /> SELECT
count(activity_id),activity_id<br /> FROM foo_activity<br /> WHERE created >= '01/01/2011' and created <
'01/08/2011'<br/> GROUP BY 2<br /> ORDER BY 2;<br /><br /> It gives me this answer, which is correct:<br /><br />
 count| activity_id<br /> -------+---------------------<br />  1502 |                   1<br />    11 |                
 2<br />     2 |                   3<br />   815 |                   4<br />  4331 |                   7<br />    30 |
                 9<br />  1950 |                  10<br />     7 |                  11<br />    67 |                
 12<br/><br /> But what I need to see is if there are no activities for the particular<br /> activity_id that week,
thatit lists the count as 0 and lists the<br /> activity_id associated like this:<br /><br />  count | activity_id<br
/>-------+---------------------<br />  1502 |                   1<br />    11 |                   2<br />     2 |      
           3<br />   815 |                   4<br />     0 |                   5<br />     0 |                   6<br
/> 4331 |                   7<br />     0 |                   8<br />    30 |                   9<br />  1950 |        
        10<br />     7 |                  11<br />    67 |                  12<br /><br /> Thanking you in advance for
anyhelp on this. The caffiene seems to be not<br /> working well today.<br /><br /> Aaron<br /><font
color="#888888"><br/><br /> --<br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/><br clear="all" /><br />-- <br />Peter Steinheuser<br /><a
href="mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a><br/>