1

My table, t, has the following format:

enter image description here

I would like to transform this table into:

enter image description here

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?

  • And your date data goes where? – Vérace Jul 29 '16 at 11:45
2

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;
|improve this answer|||||
  • 1
    Strictly speaking, each of your CASEs is returning a value regardless of whether the condition is true or false: when it's true, the CASE is returning 1, otherwise 0. Both 1 and 0 are values. – Andriy M Aug 4 '16 at 7:29
  • Good point. One can think about to omit the ELSE Statement, because the 0 is not needed, but this could lead into a Case not found for CASE statement error. – s.meissner Aug 8 '16 at 7:18
  • In MySQL there's a CASE statement and a CASE operator. Absence of ELSE can be a problem only for a CASE statement, and what you have here is a CASE operator. If you omit ELSE in a CASE operator, then ELSE NULL is implied. Still, in your situation it's better to have ELSE 0, so that, when no row matches the condition, a 0 is returned instead of a null. – Andriy M Aug 8 '16 at 11:56
  • To make it clear, in my first comment I had an issue only with your terminology and not with your solution, which makes perfect sense to me. – Andriy M Aug 8 '16 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.