Problem with returning query counting multiple columns

[adam]

Hello,

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

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

- affiliate_sub_i d 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_i d 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_i d AS SubID,
COUNT( response_code ) = '1' AS Response_1,
COUNT( response_code ) = '2' AS Response_2,
FROM table
WHERE affiliate_id = '1000'
AND application_dat e >= '2004-11-01'
AND application_dat e <= '2004-11-29'


I'm stuck. Any suggestions? :-)


Thanks,
Adam

[Bill Karwin]

Re: Problem with returning query counting multiple columns

adam wrote:[color=blue]
> SELECT affiliate_sub_i d AS SubID,
> COUNT( response_code ) = '1' AS Response_1,
> COUNT( response_code ) = '2' AS Response_2,
> FROM table
> WHERE affiliate_id = '1000'
> AND application_dat e >= '2004-11-01'
> AND application_dat e <= '2004-11-29'
>
>
> I'm stuck. Any suggestions? :-)[/color]

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_d ate 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]

Re: Problem with returning query counting multiple columns

Bill Karwin wrote:[color=blue]
> adam wrote:
>[color=green]
>> SELECT affiliate_sub_i d AS SubID,
>> COUNT( response_code ) = '1' AS Response_1,
>> COUNT( response_code ) = '2' AS Response_2,
>> FROM table
>> WHERE affiliate_id = '1000'
>> AND application_dat e >= '2004-11-01'
>> AND application_dat e <= '2004-11-29'
>>
>>
>> I'm stuck. Any suggestions? :-)[/color]
>
>
> 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_d ate 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.[/color]


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]

Re: Problem with returning query counting multiple columns

adam wrote:[color=blue]
> Thanks very much! I'm going to try this and see how it works for me. I
> really appreciate your code and comments![/color]

I'm glad to help!
[color=blue][color=green]
>> GROUP BY T.sub;[/color][/color]

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.