What is the correct way to use LEFT JOINS, GROUP BY, and ORDER BY together?

I have an SQL Query (MySQL) which uses a combination of LEFT JOIN, GROUP BUY, ORDER BY, and LIMIT. When I remove LEFT JOIN and GROUP BY it works fine. What is the correct way to add in my additional LEFT JOIN and GROUP logic to make this work?

SELECT tbl_headlines.*, count(tbl_weekly_builds.id) as related_count 
    FROM tbl_headlines 
    LEFT JOIN tbl_weekly_builds ON tbl_headlines.id = tbl_weekly_builds.headline 
    GROUP BY tbl_headlines.id 
    WHERE tbl_headlines.deleted is null 
    ORDER BY LOWER(tbl_headlines.label) asc 
    LIMIT 0, 100;

Answer

Your syntax is incorrect – where clause will be before group by

SELECT tbl_headlines.id, count(tbl_weekly_builds.id) as related_count 
    FROM tbl_headlines 
    LEFT JOIN tbl_weekly_builds ON tbl_headlines.id = tbl_weekly_builds.headline 
    WHERE tbl_headlines.deleted is null 
    GROUP BY tbl_headlines.id 
    ORDER BY LOWER(tbl_headlines.label) asc 
    LIMIT 0, 100;