Q:

Finding the average length of a column as per a particular case in MySQL

I have a table of Users and another table of the Messages they've sent and I'm trying to find the average length of a message and the average length of a message if the user is retired. If I try this:
SELECT u.id, 
AVG(LENGTH(m.body)) AS avg_msg_length, 
AVG(LENGTH(CASE m.is_retired WHEN true THEN m.body ELSE NULL END)) AS avg_msg_length_retired 
FROM Users u LEFT OUTER JOIN Messages m 
ON u.id = m.sender_id 
GROUP BY u.id;

In the result I get the average length fine but the average length for retired people is just a column of NULLs. 
If I try this:
SELECT u.id, 
AVG(LENGTH(m.body)) AS avg_msg_length, 
AVG(LENGTH(CASE m.is_retired WHEN "true" THEN m.body ELSE NULL END)) AS avg_msg_length_retired 
FROM Users u LEFT OUTER JOIN Messages m 
ON u.id = m.sender_id 
GROUP BY u.id;

I get,

ERROR 1054 (42S22): Unknown column '“true”' in 'field list'.

I find this extremely strange since the is_retired field is a varchar.
Am I using the CASE WHEN incorrectly inside the LENGTH? I tried the second form of CASE WHEN too as:
 AVG(LENGTH(CASE WHEN m.is_retired=true THEN m.body ELSE NULL END)) AS avg_msg_length_retired 

and, 
AVG(LENGTH(CASE WHEN m.is_retired="true" THEN m.body ELSE NULL END)) AS avg_msg_length_retired 

And get the same results as above.

A:

I prefer the length() inside the case:
SELECT u.id, 
       AVG(LENGTH(m.body)) AS avg_msg_length, 
       AVG(CASE WHEN m.is_retired = 'Y' THEN LENGTH(m.body) END) AS avg_msg_length_retired 
FROM Users u LEFT OUTER JOIN
     Messages m 
     ON u.id = m.sender_id 
GROUP BY u.id;

The 'Y' should be whatever character or string indicates truth.
The problem with your query is that you are mixing character expressions (is_retired) with numeric expressions (true).  Be consistent with the types.
For instance, this evaluates to false:
 select 'true' = true;

But this evaluates to true:
select 1 = true;

