Rows as columns in MySQL My table, t, has the following format:



I would like to transform this table into:



The COUNT_XXXXX columns are determined by counting the number of rows where CLICKSKEY is not NULL organised by SUBJECT_TYPE.  The COUNT_CLICKED column counts the number of rows where CLICKSKEY is not NULL independent of SUBJECT_TYPE.

Could anybody help?
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;