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@bzzagent.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Peter Steinheuser
psteinheuser@myyearbook.com
