How to count using LEFT JOIN?

Question

I need to make a list with the company that scans the most things... I've got two tables, Exhibitors and Leads

Leads contain each "action" that every exhibitors does. Sometimes, in the Exhibitors table, there's two accounts for the same company. For example, PepsiCO might have two accounts.

The key/col that link both is called ExhibitorID.

I need to retrieve the top 20 with the most leads (so COUNT(*) in leads) but I need to group by Company in Exhibitors table.

Curerntly I am using this :

    SELECT t2.ExhibitorID, t2.Company, t2.Username, t1.Count 
      FROM exhibitors AS t2
         , ( SELECT ExhibitorID, COUNT( * ) AS Count 
               FROM leads AS l 
              WHERE l.ContractorID = 100 
                AND l.ShowID =  "20120228AB" 
              GROUP BY l.ExhibitorID 
              ORDER BY COUNT(*) DESC 
              LIMIT 20 ) AS t1 
     WHERE t2.ExhibitorID = t1.ExhibitorID 
     ORDER BY t1.Count DESC

But it doesn't group by company. Thanks for all your help.

Answer 1

You could group on Company and join on ExhibitorID:

select  e.Company
,       count(distinct l.LeadID) as LeadCount
from    Exhibitors e
join    Leads l
on      l.ExhibitorID = e.ExhibitorID
        and l.ShowID = "20120228AB" 
        and l.ContractorID = 100
group by
        e.Company
order by
        LeadCount DESC
limit   20

Answer 2

    select e.exhibitorid, count(l.leadid) 
    from leads l 
        inner join exhibitors on e.exhibitorid = l.exhibitorid 
        and l.showid = "20120228AB" and l.contractorid = 100
    group by e.exhibitorid 
    order by count(l.contractorid) DESC
    limit 20;

If there are ties, this will still only return the top 20. So if you want ties then there appears to be a MySql workaround here MySQL's alternative to T-SQL's WITH TIES

Answer 3

SELECT
    e.Company,
    COUNT(l.LeadId) AS Actions
FROM
    Exhibitors AS e 
    LEFT JOIN Leads AS l
        ON l.ExhibitorID = e.ExhibitorID
        AND l.ContractorID = 100
        AND l.ShowID = "20120228AB"
GROUP BY e.Company
ORDER BY Actions DESC
LIMIT 20;