MySQL break out group clause from subquery
        Posted  
        
            by 
                Anton Gildebrand
            
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by Anton Gildebrand
        
        
        
        Published on 2012-11-21T10:42:01Z
        Indexed on 
            2012/11/21
            11:00 UTC
        
        
        Read the original article
        Hit count: 299
        
Here is my query
SELECT 
    COALESCE(js.name,'Lead saknas'),
    count(j.id)
FROM 
    jobs j
LEFT JOIN
    job_sources js
    ON j.job_source=js.id
LEFT JOIN 
    (SELECT 
        * 
    FROM 
        quotes 
    GROUP BY 
        job_id) q 
    ON j.id=q.job_id
GROUP BY
    j.job_source
The problem is that it's allowed for each job to have more than one quote. Because of that i group the quotes by job_id. Now sure, this works. But i don't like the solution with a subquery. How can i break out the group clause from the subquery to the main query? I have tried to add q.job_id to the main group clause, both before and after the existing one but don't get the same results.
© Stack Overflow or respective owner