Re: pivot table sql -- COUNT() not working as expected

hey thanks! can't believe it was as simple as 0 -> NULL. doh.
count() works as expected now.

On Jun 20, 2004, at 11:17 AM, Markus Bertheau wrote:

> В Вск, 20.06.2004, в 17:44, kumar mcmillan пишет:
>> hi.
>> I am using a pseudo "pivot table" SQL query to generate a report butI
>> am totally stumped on why the COUNT() function isn't getting me
>> theexpected results and was wondering if anyone has some ideas...
>>
>> it is for a statistical report showing which lead source a
>> memberfollowed when creating a profile (i.e. CNN ad, Chicago Tribune
>> ad,etc), grouped by age range.
>>
>> here is the SQL that should total up rows based each
>> specificcondition, but doesn't:
>>
>> SELECT ms.display_name AS lead_source, EXTRACT(YEAR
>> FROMAGE(pd.birth_date)) AS age_range,
>> COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14'
>> THENp.profile_id ELSE 0 END) AS "Total 03/07/04",
>
> You want
>
> COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN
> p.profile_id ELSE NULL END) AS "Total 03/07/04"
>
> or
>
> COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN
> 1 ELSE NULL END) AS "Total 03/07/04"
>
> or
>
> COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN
> 42 ELSE NULL END) AS "Total 03/07/04"
>
> or
>
> COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN
> true ELSE NULL END) AS "Total 03/07/04"
>
> which is all the same.
>
> --
> Markus Bertheau <twanger(at)bluetwanger(dot)de>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>