Count, SUM, LEFT JOIN and GROUP BY in query not working right

Question

I have tried a few things but I can't seem to figure out what's causing the problem. When I remove the totalHours part, the query works fine. But with it, it displays the right number of hours but the wrong number of Jobs, Selected and Winners.

Could someone please tell me what I am doing wrong?

Thanks in advance.

Here is my query;

SELECT 

    crmCandidate.candidateID, 
    crmCandidate.candidateName, 

    COUNT(DISTINCT crmJoin.joinID) AS Jobs, 

    SUM(IF(crmJoin.joinExtra = 'select', 1, 0)) AS Selected, 
    SUM(IF(crmJoin.joinExtra = 'winner', 1, 0)) AS Winner, 

    ROUND(SUM(crmDays.total)) AS totalDays 

FROM crmCandidate 
LEFT JOIN crmJoin ON (crmJoin.joinChild = crmCandidate.candidateID) 
LEFT JOIN crmJob ON (crmJob.jobID = crmJoin.joinParent) 
LEFT JOIN crmDays ON (crmDays.dayCandidateID = crmJoin.joinChild) 

WHERE 

    crmDays.dayJobID = crmJob.jobID AND 
    crmDays.dayCandidateID = crmCandidate.candidateID 

GROUP BY 
    crmCandidate.candidateID 

ORDER BY DESC 

LIMIT 100

Answer 1

try this one :

SELECT 
    crmCandidate.candidateID, 
    crmCandidate.candidateName, 
    COUNT(DISTINCT crmJoin.joinID) AS Jobs, 
    Sum(Case When crmJoin.joinExtra = 'select' Then 1 else 0 end) as Selected,
    Sum(Case When crmJoin.joinExtra = 'winner' Then 1 else 0 end) as winner,
    ROUND(SUM(crmDays.total)) AS totalDays 
FROM crmCandidate 
LEFT JOIN crmJoin   
    ON crmJoin.joinChild = crmCandidate.candidateID
LEFT JOIN crmJob    
    ON crmJob.jobID = crmJoin.joinParent
Inner JOIN crmDays  
    On  crmDays.dayCandidateID  = crmCandidate.candidateID 
    AND crmDays.dayJobID        = crmJob.jobID
GROUP BY crmCandidate.candidateID, crmCandidate.candidateName
ORDER BY candidateID DESC 
LIMIT 100