Rows as columns in MySQL
You can use the CASE statement and return a value every time the condition is true. In the end you just have to sum those values.

SELECT
  DEALER_ID, 
  SUM(CASE SUBJECT_TYPE WHEN 'Marketing' THEN 1 ELSE 0 END) AS COUNT_MARKETING, 
  SUM(CASE SUBJECT_TYPE WHEN 'Random' THEN 1 ELSE 0 END) AS COUNT_RANDOM, 
  SUM(CASE SUBJECT_TYPE WHEN 'Alert' THEN 1 ELSE 0 END) AS COUNT_ALERT,
  COUNT(*) AS COUNT_CLICKED 
FROM t 
WHERE CLICKSKEY IS NOT NULL 
GROUP BY DEALER_ID;