How do I build a dynamic query in Drupal 7 equivalent to this SQL query? I want to make this SQL query into a dynamic drupal query:

SELECT name, u.uid, COUNT( n.nid ) AS total_written 
FROM users u 
LEFT JOIN node n ON n.uid = u.uid
GROUP BY u.uid
ORDER BY total_written DESC


I do not know where i should append the COUNT. This is what I tried:

$query = db_select('users', 'u');
$query-&gt;leftJoin('node', 'n', 'n.uid = u.uid');
$query
  -&gt;fields('u', array('uid', 'name',))
  -&gt;fields('u', array('COUNT(n.nid)', 'total_written')) # Wrong
  -&gt;groupBy('u.uid')
  -&gt;orderBy('total_written', 'DESC');


How do I append the COUNT to the dynamic query?
$query = db_select('users', 'u');
$query-&gt;leftJoin('node', 'n', 'n.uid = u.uid');
$query
  -&gt;fields('u', array('uid', 'name',))
  -&gt;groupBy('u.uid')
  -&gt;orderBy('total_written', 'DESC');

// Add the COUNT expression
$query-&gt;addExpression('COUNT(n.id)', 'total_written');
$results = $query-&gt;execute();