By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,652 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,652 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
В Вск, 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 <tw*****@bluetwanger.de>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #2

P: n/a
В Вск, 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 <tw*****@bluetwanger.de>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.