From: Aaron Burnett <aburnett@bzzagent.com>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Fri, 11 Feb 2011 13:46:15 -0500
Subject: Drawing a blank on some SQL
Thread-Topic: Drawing a blank on some SQL
Thread-Index: AcvKG/X7OQjOULpTn0qTfyMto5ruiQ==
Message-ID: <C97AD207.233C8%aburnett@bzzagent.com>
Accept-Language: en-US
Content-Language: en
acceptlanguage: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


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 querie=
d
on a weekly basis:

SELECT count(activity_id), activity_id
FROM foo_activity=20
WHERE created >=3D '01/01/2011' and created < '01/08/2011'
GROUP BY 2=20
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