From: Aaron Burnett <aburnett@bzzagent.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Drawing a blank on some SQL
Date: Fri, 11 Feb 2011 15:47:45 -0500
Message-ID: <C97AEE81.233E8%aburnett@bzzagent.com> (raw)
In-Reply-To: <AANLkTinACoRHLx3=_XYs08x+a_Lr_Tv9wmAb-UnAotCp@mail.gmail.com>
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.kussama@gmail.com> wrote:
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aburnett@bzzagent.com
Subject: Re: Drawing a blank on some SQL
In-Reply-To: <C97AEE81.233E8%aburnett@bzzagent.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox