Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id D4F70133616B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Feb 2011 16:47:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 39589-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Feb 2011 20:47:51 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from exprod6og115.obsmtp.com (exprod6og115.obsmtp.com [64.18.1.35])
	by mail.postgresql.org (Postfix) with SMTP id 3385C1337B90
	for <pgsql-sql@postgresql.org>; Fri, 11 Feb 2011 16:47:49 -0400 (AST)
Received: from source ([66.152.196.38]) (using TLSv1) by
	exprod6ob115.postini.com ([64.18.5.12]) with SMTP
	ID DSNKTVWgdYywfjUhSN2V5UyVUeeI7swqcWNo@postini.com;
	Fri, 11 Feb 2011 12:47:50 PST
Received: from EXCHMAIL.bzzagent.lan ([172.16.92.56]) by EXCHCAS.bzzagent.lan
	([192.168.1.22]) with mapi; Fri, 11 Feb 2011 15:47:48 -0500
From: Aaron Burnett <aburnett@bzzagent.com>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Fri, 11 Feb 2011 15:47:45 -0500
Subject: Re: Drawing a blank on some SQL
Thread-Topic: [SQL] Drawing a blank on some SQL
Thread-Index: AcvKJ41NhRj8X4JRQWasd24zgjHNPAABWHYa
Message-ID: <C97AEE81.233E8%aburnett@bzzagent.com>
In-Reply-To: <AANLkTinACoRHLx3=_XYs08x+a_Lr_Tv9wmAb-UnAotCp@mail.gmail.com>
Accept-Language: en-US
Content-Language: en
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_C97AEE81233E8aburnettbzzagentcom_"
MIME-Version: 1.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.199 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3
X-Spam-Level: 
X-Archive-Number: 201102/57
X-Sequence-Number: 35133

--_000_C97AEE81233E8aburnettbzzagentcom_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



Thank you all very much for your help.

The suggestion from Osvaldo below was the best for my situation (not having=
 any soret of xref table to join)...

Best Regards,

Aaron


On 2/11/11 1:09 PM, "Osvaldo Kussama" <osvaldo.kussama@gmail.com> wrote:

2011/2/11, Aaron Burnett <aburnett@bzzagent.com>:
>
> Hi,
>
> I'm just drawing a blank entirely today and would appreciate some help on
> this.
>
> The long and short; there are 12 distinct activities that need to be quer=
ied
> on a weekly basis:
>
> SELECT count(activity_id), activity_id
> FROM foo_activity
> WHERE created >=3D '01/01/2011' and created < '01/08/2011'
> GROUP BY 2
> ORDER BY 2;
>
> It gives me this answer, which is correct:
>
>  count | activity_id
> -------+---------------------
>   1502 |                   1
>     11 |                   2
>      2 |                   3
>    815 |                   4
>   4331 |                   7
>     30 |                   9
>   1950 |                  10
>      7 |                  11
>     67 |                  12
>
> But what I need to see is if there are no activities for the particular
> activity_id that week, that it lists the count as 0 and lists the
> activity_id associated like this:
>
>  count | activity_id
> -------+---------------------
>   1502 |                   1
>     11 |                   2
>      2 |                   3
>    815 |                   4
>      0 |                   5
>      0 |                   6
>   4331 |                   7
>      0 |                   8
>     30 |                   9
>   1950 |                  10
>      7 |                  11
>     67 |                  12
>
> Thanking you in advance for any help on this. The caffiene seems to be no=
t
> working well today.
>


Try:
SELECT sum(case when created >=3D '2011-01-01' and created <
'2011-01-08' then 1 else 0 end), activity_id
FROM foo_activity
GROUP BY 2
ORDER BY 2;

Osvaldo


--_000_C97AEE81233E8aburnettbzzagentcom_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<HTML>
<HEAD>
<TITLE>Re: [SQL] Drawing a blank on some SQL</TITLE>
</HEAD>
<BODY>
<FONT FACE=3D"Calibri, Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-size:=
11pt'><BR>
<BR>
Thank you all very much for your help.<BR>
<BR>
The suggestion from Osvaldo below was the best for my situation (not having=
 any soret of xref table to join)...<BR>
<BR>
Best Regards,<BR>
<BR>
Aaron<BR>
<BR>
<BR>
On 2/11/11 1:09 PM, &quot;Osvaldo Kussama&quot; &lt;<a href=3D"osvaldo.kuss=
ama@gmail.com">osvaldo.kussama@gmail.com</a>&gt; wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Calibri, Verdana, Helvetica, Arial"=
><SPAN STYLE=3D'font-size:11pt'>2011/2/11, Aaron Burnett &lt;<a href=3D"abu=
rnett@bzzagent.com">aburnett@bzzagent.com</a>&gt;:<BR>
&gt;<BR>
&gt; Hi,<BR>
&gt;<BR>
&gt; I'm just drawing a blank entirely today and would appreciate some help=
 on<BR>
&gt; this.<BR>
&gt;<BR>
&gt; The long and short; there are 12 distinct activities that need to be q=
ueried<BR>
&gt; on a weekly basis:<BR>
&gt;<BR>
&gt; SELECT count(activity_id), activity_id<BR>
&gt; FROM foo_activity<BR>
&gt; WHERE created &gt;=3D '01/01/2011' and created &lt; '01/08/2011'<BR>
&gt; GROUP BY 2<BR>
&gt; ORDER BY 2;<BR>
&gt;<BR>
&gt; It gives me this answer, which is correct:<BR>
&gt;<BR>
&gt; &nbsp;count | activity_id<BR>
&gt; -------+---------------------<BR>
&gt; &nbsp;&nbsp;1502 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;11 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3<B=
R>
&gt; &nbsp;&nbsp;&nbsp;815 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4<BR>
&gt; &nbsp;&nbsp;4331 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;30 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9<BR>
&gt; &nbsp;&nbsp;1950 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;67 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12<BR>
&gt;<BR>
&gt; But what I need to see is if there are no activities for the particula=
r<BR>
&gt; activity_id that week, that it lists the count as 0 and lists the<BR>
&gt; activity_id associated like this:<BR>
&gt;<BR>
&gt; &nbsp;count | activity_id<BR>
&gt; -------+---------------------<BR>
&gt; &nbsp;&nbsp;1502 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;11 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3<B=
R>
&gt; &nbsp;&nbsp;&nbsp;815 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<B=
R>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6<B=
R>
&gt; &nbsp;&nbsp;4331 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8<B=
R>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;30 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9<BR>
&gt; &nbsp;&nbsp;1950 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11<BR>
&gt; &nbsp;&nbsp;&nbsp;&nbsp;67 | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12<BR>
&gt;<BR>
&gt; Thanking you in advance for any help on this. The caffiene seems to be=
 not<BR>
&gt; working well today.<BR>
&gt;<BR>
<BR>
<BR>
Try:<BR>
SELECT sum(case when created &gt;=3D '2011-01-01' and created &lt;<BR>
'2011-01-08' then 1 else 0 end), activity_id<BR>
FROM foo_activity<BR>
GROUP BY 2<BR>
ORDER BY 2;<BR>
<BR>
Osvaldo<BR>
<BR>
</SPAN></FONT></BLOCKQUOTE>
</BODY>
</HTML>


--_000_C97AEE81233E8aburnettbzzagentcom_--