Re: Drawing a blank on some SQL

Thank you all very much for your help.

The suggestion from Osvaldo below was the best for my situation (not having any soret of xref table to join)...

Best Regards,

Aaron

On 2/11/11 1:09 PM, "Osvaldo Kussama" <osvaldo(dot)kussama(at)gmail(dot)com> wrote:

2011/2/11, Aaron Burnett <aburnett(at)bzzagent(dot)com>:
>
> 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.
>

Try:
SELECT sum(case when created >= '2011-01-01' and created <
'2011-01-08' then 1 else 0 end), activity_id
FROM foo_activity
GROUP BY 2
ORDER BY 2;

Osvaldo