Getting an AVG of TIMESTAMPDIFF of related table?

Question

I have two tables features and responses, where there may be multiple responses for each feature (or maybe zero responses). The responses table has a began and ended column which contain timestamps (there will be at least a began timestamp, and the ended may be NULL).

I'm trying to extract a list of features with summary detail of their respective responses (if any). I'm not using GROUP BY on the responses table as I want to include features without responses.

This is the SQL I'm trying, but it's reporting an SQL syntax error near the TIMESTAMPDIFF clause.

SELECT f.id, f.name, f.description, is_active, 
  (SELECT COUNT(r.id) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id 
     AND r.result IS NULL) AS nr_s, 

  (SELECT COUNT(r.id) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id) AS nr_r, 

  (SELECT SUM(r.is_D) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id) AS is_D, 

  (SELECT AVG(TIMESTAMPDIFF(SECONDS,r.began,r.ended)) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id) AS duration

FROM features f 
WHERE f.is_deleted IS NULL AND f.id_survey=44
ORDER BY f.id ASC

What am I doing wrong?

Answer 1

SECONDS is not a valid unit. You want SECOND:

SELECT f.id, f.name, f.description, is_active, 
  (SELECT COUNT(r.id) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id 
     AND r.result IS NULL) AS nr_s, 

  (SELECT COUNT(r.id) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id) AS nr_r, 

  (SELECT SUM(r.is_D) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id) AS is_D, 

  (SELECT AVG(TIMESTAMPDIFF(SECOND,r.began,r.ended)) FROM responses r 
   LEFT JOIN participants p ON r.id_participant = p.id 
   WHERE p.is_ignored IS NULL 
     AND r.is_deleted IS NULL 
     AND r.id_feature = f.id) AS duration

FROM features f 
WHERE f.is_deleted IS NULL AND f.id_survey=44
ORDER BY f.id ASC