Re: pivot table sql -- COUNT() not working as expected - Mailing list pgsql-general

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:


<excerpt><fontfamily><param>Lucida Grande</param>В</fontfamily>
<fontfamily><param>Lucida Grande</param>Вск</fontfamily>, 20.06.2004,
<fontfamily><param>Lucida Grande</param>в</fontfamily> 17:44, kumar
mcmillan <fontfamily><param>Lucida Grande</param>пишет</fontfamily>:

<excerpt>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",

</excerpt>

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@bluetwanger.de>



---------------------------(end of
broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?


               http://www.postgresql.org/docs/faqs/FAQ.html


</excerpt>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@bluetwanger.de>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>