Problem with returning query counting multiple columns

[adam]

Hello,

I have a MySQL table that has (among other fields) an affiliate_id,
affiliate_sub_id, and a response_code field.

- affiliate_id stores the unique number of each of my affiliates.

- affiliate_sub_id stores the affiliate ID of their affiliates.

- response_code holds a number between 1 - 6.

I have an affiliate (e.g., 1000). For affiliate 1000, I would like to
return an array with three columns:

SubID Response_1 Response_2

Each row returned, should contain each affiliate_sub_id for affiliate_id
= '1000'. Response_1 should count how many "1" SubID had, and Response_2
should count how many "2" SubID had.

Below is the code I wrote, but I get the following error in phpMyAdmin:
"WHERE affiliate_id = '1000' AND application_"
SELECT affiliate_sub_id AS SubID,
COUNT( response_code ) = '1' AS Response_1,
COUNT( response_code ) = '2' AS Response_2,
FROM table
WHERE affiliate_id = '1000'
AND application_date >= '2004-11-01'
AND application_date <= '2004-11-29'
I'm stuck. Any suggestions? :-)
Thanks,
Adam

[Bill Karwin]

adam wrote:

SELECT affiliate_sub_id AS SubID,
COUNT( response_code ) = '1' AS Response_1,
COUNT( response_code ) = '2' AS Response_2,
FROM table
WHERE affiliate_id = '1000'
AND application_date >= '2004-11-01'
AND application_date <= '2004-11-29'
I'm stuck. Any suggestions? :-)

Take advantage of the fact that COUNT() doesn't count NULLs:

SELECT T.affiliate_sub_id AS SubID,
COUNT( IF(T.resp='1', 1, NULL) ) AS Response_1,
COUNT( IF(T.resp='2', 2, NULL) ) AS Response_2
FROM `table` AS T
WHERE T.affiliate_id = '1000'
AND T.application_date BETWEEN '2004-11-01' AND '2004-11-29'
GROUP BY T.sub;

I tried this out in my test database and it seems to return the required
results you described.

Actually, the second argument to IF() in both cases could be any
non-null value, it doesn't have to be the same value you were testing
for. COUNT() is only counting non-nulls, not the value.

Also notice I put `table` in backticks, because table is a SQL reserved
word.

Regards,
Bill K.

[adam]

Bill Karwin wrote:

adam wrote:

SELECT affiliate_sub_id AS SubID,
COUNT( response_code ) = '1' AS Response_1,
COUNT( response_code ) = '2' AS Response_2,
FROM table
WHERE affiliate_id = '1000'
AND application_date >= '2004-11-01'
AND application_date <= '2004-11-29'
I'm stuck. Any suggestions? :-)

Take advantage of the fact that COUNT() doesn't count NULLs:

SELECT T.affiliate_sub_id AS SubID,
COUNT( IF(T.resp='1', 1, NULL) ) AS Response_1,
COUNT( IF(T.resp='2', 2, NULL) ) AS Response_2
FROM `table` AS T
WHERE T.affiliate_id = '1000'
AND T.application_date BETWEEN '2004-11-01' AND '2004-11-29'
GROUP BY T.sub;

I tried this out in my test database and it seems to return the required
results you described.

Actually, the second argument to IF() in both cases could be any
non-null value, it doesn't have to be the same value you were testing
for. COUNT() is only counting non-nulls, not the value.

Also notice I put `table` in backticks, because table is a SQL reserved
word.

Regards,
Bill K.

Bill,

Thanks very much! I'm going to try this and see how it works for me. I
really appreciate your code and comments!

Take care,
Adam

[Bill Karwin]

adam wrote:

Thanks very much! I'm going to try this and see how it works for me. I
really appreciate your code and comments!

I'm glad to help!

GROUP BY T.sub;

I want to correct myself: this should be GROUP BY T.affiliate_sub_id.
When I created a mockup in my test database, I abbreviated the table
name and field names, but when I posted the query yesterday I forgot to
edit this field to match your field name.

Cheers,
Bill K.