From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Drawing a blank on some SQL
Date: Fri, 11 Feb 2011 12:17:08 -0700
Message-ID: <4D558B34.50901@gmail.com> (raw)
In-Reply-To: <C97AD207.233C8%aburnett@bzzagent.com>
References: <C97AD207.233C8%aburnett@bzzagent.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com
Subject: Re: Drawing a blank on some SQL
In-Reply-To: <4D558B34.50901@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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