Sam Wilson Sam Wilson - 6 months ago 47
SQL Question

LEFT JOIN error with workbench

i am trying to get a list of cases with total and passed cases. Here is the query i wrote-

select totalcases.feature,passedcases.passed,

from ((select feature, count(distinct templateid) as Total
from results
where build = 'random' group by feature
) AS totalcases)
LEFT JOIN ((select feature,count(distinct templateid) as PASSED
from results
where build='random' and result='PASS' group by feature
) AS passedcases) using feature;

mysql says there's some syntax error . Also, does workbench not allow a query to be broken in different lines like the terminal ?


You can probably do much simpler using group by

select  feature,
            sum(case when result = 'PASS' then  1 else 0 end) as passed,
            count(*) as totalcases 
from        results
where       build = 'random'
group       by feature
order       by feature