Message-ID: <4D558B34.50901@gmail.com>
Date: Fri, 11 Feb 2011 12:17:08 -0700
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20101125 SUSE/3.0.11 Lightning/1.0b1
	Thunderbird/3.0.11
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
References: <C97AD207.233C8%aburnett@bzzagent.com>
In-Reply-To: <C97AD207.233C8%aburnett@bzzagent.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Subject: Re: Drawing a blank on some SQL


On 02/11/2011 11:46 AM, Aaron Burnett wrote:
> 
> 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 queried
> on a weekly basis:
> 
> SELECT count(activity_id), activity_id
> FROM foo_activity 
> WHERE created >= '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 not
> working well today.
> 
> Aaron
> 
> 
Do you have a table which lists exhaustively the know activity_id values
in the system.  You may need to 'select distinct activity_id from
foo_activity' to get the complete list, then left join against that (or
the existing list) in your count.  You need something to supply the 5,6
and 8.