mysql join count

Question

I have two table

Table Leave

ID   | TYPE
1      Annual 
2      Sick 
3      Unpaid
4      Marriage

Table LeaveData

IDLEAVEDATA   | LEAVETYPE*
1                 1      
2                 1
3                 2
4                 2

LEAVETYPE is foreign key (refer to ID in table leave)

How do i count the occurence of ID in table LeaveData?

Output example :

TYPE         |  COUNT
Annual          2
Sick            2
Unpaid          0 or null
Marriage        0 or null

Answer 1

Try this:

 SELECT  L.[Type] AS [Type]
         ,ISNULL(COUNT(*),0) AS [Count]
 FROM    Leave AS L
 LEFT JOIN LeaveData AS LD ON LD.LeaveType = L.ID
 ORDER BY 2 DESC

Answer 2

try,

SELECT  l.TYPE , COUNT(ld.LEAVETYPE) as COUNT
FROM    Leave AS l
LEFT JOIN LeaveData AS ld ON ld.LEAVETYPE = L.ID
GROUP BY  ld.LEAVETYPE