Re: Drawing a blank on some SQL

2011/2/11, Aaron Burnett <aburnett@bzzagent.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