Trying to count two different amounts

1

I have a complex query where I am trying to count the number of qualified and non qualified in a table called a. If you look at my code you can see that I am only counting the number of qualified=1. What I really need is the number of qualified=1 and the number of qualified=0, for each employee. When I run the code below I am getting an employee's email and the COUNT is 2. But I need the count of the a table, not the count of the srat table. I am not sure how to accomplish this. I'd like to use COUNT(a.qualified=1), COUNT(a.qualified=0) and GROUP BY srat.employee. Any suggestions?

$sql="select srat.employee_email, COUNT (a.*)

    from survey_results_activities_temp srat, survey_results sr, relation r, standard_activities sa, activity a
    where sr.user_email=srat.employee_email AND sr.item_type='AC' AND sr.campaign= '$_SESSION[campaign]' AND sr.email='$_SESSION[userid]' 
    AND sr.item=sa.activity AND sa.standard_id=r.activity_id AND r.id=a.id AND a.qualified=1";
2

select
[number of qualified] , count(*)
group by
[number of qualified]

this will show like this

1 20
0 50

if you want like this
1 20 0 50
then sql will be different

3

Thanks Harish. I modified as per your direction but I am getting an error:

select SUM(a.qualified), COUNT (a.*)
from survey_results_activities_temp srat, survey_results sr, relation r, standard_activities sa, activity a
where sr.user_email=srat.employee_email AND sr.item_type='AC' AND sr.campaign= '$_SESSION[campaign]' AND sr.email='$_SESSION[userid]'
AND sr.item=sa.activity AND sa.standard_id=r.activity_id AND r.id=a.id
GROUP BY SUM(a.qualified)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)
from survey_results_activities_temp srat, survey_results sr, relation r, st' at line 1

I modified the a table to have a qualified and a nonqualified column, thinking I could just SUM(qualified) and SUM(nonqualified) to create a ratio of qualified/total which is what I really need on a per employee basis, but I am not sure how to run this on a srat.employee basis.

4

hi

this is SQL Server Forum TSQL

MySQL will have the same thing in different syntax

1- Please Google Serach
1- Please Google Serach850×681 13.5 KB
Please Google Serach
3 - Please google search
3 - Please google search1143×715 56.2 KB

5 
select  sum(case when a.qualified=1 then 1 else 0 end 
, sum(case when a.qualified=0 then 1 else 0 end 
from survey_results_activities_temp srat
, survey_results sr
, relation r
, standard_activities sa
, activity a
where	sr.user_email=srat.employee_email 
AND		sr.item_type='AC' 
AND sr.campaign= '$_SESSION[campaign]' 
AND sr.email='$_SESSION[userid]' 
AND sr.item=sa.activity 
AND sa.standard_id=r.activity_id 
AND r.id=a.id 
--AND a.qualified=1