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

В Вск, 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>