SELECT TC.DESCRIPTION,count(TE.CategoryID) AS COUNT
  FROM tblEvent TE
  right JOIN tblCategory TC on TE.CategoryID=TC.NO
  WHERE TE.AssetID IN (
  SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3)
  GROUP BY  TE.CategoryID,TC.DESCRIPTION