hi.
I am using a pseudo "pivot table" SQL query to generate a report but I
am totally stumped on why the COUNT() function isn't getting me the
expected results and was wondering if anyone has some ideas...
it is for a statistical report showing which lead source a member
followed 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 specific
condition, but doesn't:
SELECT ms.display_name AS lead_source, EXTRACT(YEAR FROM
AGE(pd.birth_date)) AS age_range,
COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN
p.profile_id ELSE 0 END) AS "Total 03/07/04",
COUNT(CASE WHEN p.added_on BETWEEN '2004-03-14' AND '2004-03-21' THEN
p.profile_id ELSE 0 END) AS "Total 03/14/04",
COUNT(CASE WHEN p.added_on BETWEEN '2004-03-21' AND '2004-03-28' THEN
p.profile_id ELSE 0 END) AS "Total 03/21/04",
COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-06-27' THEN
p.profile_id ELSE 0 END) AS "Total 03/07/2004 - 06/27/2004",
COUNT(p.profile_id) as "Total "
FROM ss_profile p, ss_profile_detail pd, ss_profile_multi_select s,
ss_multi_select ms
WHERE p.profile_id = pd.profile_id AND s.profile_id = p.profile_id AND
s.multi_select_id = ms.multi_select_id AND ms.selection_type =
'how_did_you_hear'
GROUP BY ms.display_name, age_range
;
the output of the query is this:
http://farmdev.com/test-report-w-count.txt
(you will prob need to paste that into a fix-width font to see it
properly)
the numbers are all wrong... they are the same for each column for
some reason across the board but I don't know why.
As a workaround I created a column called "counter", which will always
have the value "1" and did a SUM of that to mimic COUNT .... and it
works fine! so what is the problem with COUNT? here is the workaround
SQL:
SELECT ms.display_name AS lead_source, EXTRACT(YEAR FROM
AGE(pd.birth_date)) AS age_range,
SUM(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN
p.counter ELSE 0 END) AS "Total 03/07/04",
SUM(CASE WHEN p.added_on BETWEEN '2004-03-14' AND '2004-03-21' THEN
p.counter ELSE 0 END) AS "Total 03/14/04",
SUM(CASE WHEN p.added_on BETWEEN '2004-03-21' AND '2004-03-28' THEN
p.counter ELSE 0 END) AS "Total 03/21/04",
SUM(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-06-27' THEN
p.counter ELSE 0 END) AS "Total 03/07/2004 - 06/27/2004",
SUM(p.counter) as "Total "
FROM ss_profile p, ss_profile_detail pd, ss_profile_multi_select s,
ss_multi_select ms
WHERE p.profile_id = pd.profile_id AND s.profile_id = p.profile_id AND
s.multi_select_id = ms.multi_select_id AND ms.selection_type =
'how_did_you_hear'
GROUP BY ms.display_name, age_range
;
and here is how that report looks, which shows the correct numbers:
http://farmdev.com/test-report-w-sum.txt
... ok... the limited date range and limited lead sources isn't the
best for example purposes but this should give an idea of what I'm
trying to accomplish. also, to avoid confusion, "age range", actually
does get compressed in the PHP script so it looks more like 18-21 ...
number.
thanks in advance,
Kumar
