Slightly complex MySQL query

Question

Hi,

employees
e_id    first last
1   John Smith
2   Bob Smith
3   Alex Smith
4   John Doe
5   Ron Doe

clockpunch
e_id    time for adjustment
1   0650 in early
3   0710 in late
4   0725 in early
1   1100 lunch ---
2   1150 in late
2   1400 lunch ---
4   1320 out ---

I need a SINGLE query that will list all employee names, along with a count of how many times that user has been early, descending order by the count of times early. How would this be done ?

Answer 1

This might work (untested)

select first,last,count(*) from employees e, clockpunch c 
where e.e_id = c.e_id and adjustment = 'early' 
group by first,last
order by count(*) desc

Answer 2

    select a.first,a.last,sum(case b.adjustment when 'early' then 1 else 0 end) as ct 
from employees a, clockpunch b where a.e_id=b.e_id
    group by a.first, a.last