What is wrong with this query?

I have a table called survey_results. It has fields called item and also user_email. I have another table called business_components and it has fields called bus_comp and qualified. For each item in survey_results I want to count the number of rows in business_components where the business_components.bus_comp=survey_result.item AND business_components.qualified="yes". Here is what I came up with:

SELECT sr.user_email,COUNT(bc.bus_comp) FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp AND bc.qualified='yes'
GROUP BY sr.user_email

This seems to work and it yields this

sr.usr COUNT(bc.bus_comp)
2
1

I'd like to add another column to the result. I'd like to Count the total number of sr.item=bc.bus_comp without the bc.qualified, for each user. How can I do this?

 

I'd suggest you use a separate query for that, because even if you run that as sub query inside this one, still it will be load of 2 queries and tables will need to be rescanned.
Your "where" clause is different for this selection, so same query will not work. Tables will be rescanned with a sub query also.
So why complicate a simple query. Run a different one.

 

Well, I managed to get two more columns by using:
SELECT sr.user_email,COUNT(bc.qualified="yes") FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email
SELECT sr.user_email,COUNT(bc.qualified<>"yes") FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email

This makes no sense. Is it not possible to have a qualification inside a count?

 

Thank you for the response. I have thought about that. Here is what I'd like to do:
SELECT sr.user_email,COUNT(bc.qualified="yes") FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email
SELECT sr.user_email,COUNT(bc.qualified) FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email

But, how do I bring the results together? Should I create a TEMP table with the ratio of qualified/total? I also have a need to do some additional calcs such as multiplying each resulting row by a wage. Thanks again.

 

In both these queries your WHERE clause is same, so you can try this:


SELECT sr.user_email,
COUNT(bc.qualified = 'yes') as qYes,
COUNT(bc.qualified) as qAny
FROM survey_results sr, business_components bc
WHERE sr.item=bc.bus_comp
GROUP BY sr.user_email

or this,

SELECT sr.user_email,
SUM(if(bc.qualified = 'yes', 1, 0)) as qYes,
COUNT(bc.qualified) as qAny
FROM survey_results sr, business_components bc
WHERE sr.item=bc.bus_comp
GROUP BY sr.user_email