From: | Peter Steinheuser <psteinheuser(at)myyearbook(dot)com> |
---|---|
To: | Aaron Burnett <aburnett(at)bzzagent(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Drawing a blank on some SQL |
Date: | 2011-02-11 19:11:55 |
Message-ID: | AANLkTinyJcEE4v9Yb6T6aV2pYeVkqq8rMYt3o=n7HO+a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Not tested.
1. select count(t2.activity_id),
t1.activity_id
from (select distinct activity_id from foo_activity) as t1, -- assumes
all activities exist somewhere in table
left join foo_activity t2 on (t1.activity_id = t2.activity_id)
WHERE created >= '01/01/2011' and created < '01/08/2011'
group by 2
order by 2;
2. -- use generate_series for simple numbering scheme
select count(t2.activity_id)
t1.x as 'activity_id"
from generate_series(1,12) as t1(x),
left join foo_activity t2 on (t1.x = t2.activity_id)
WHERE created >= '01/01/2011' and created < '01/08/2011'
group by 2
order by 2;
On Fri, Feb 11, 2011 at 1:46 PM, Aaron Burnett <aburnett(at)bzzagent(dot)com>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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Peter Steinheuser
psteinheuser(at)myyearbook(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2011-02-11 19:17:08 | Re: Drawing a blank on some SQL |
Previous Message | Aaron Burnett | 2011-02-11 18:46:15 | Drawing a blank on some SQL |