Any ways to create count column like mysql "COUNT(*) as counted" Column with d7's db_select()? hello everybody i have to make tableselect form with some extra fields. To make table sortable I have to show which DB fields is used for the column in header:

$header = array(
    'uid' =&gt; array('data' =&gt; t('UID'), 'field' =&gt; 'u.uid'),
    'name' =&gt; array('data' =&gt; t('Name'), 'field' =&gt; 'u.name'),
    'mail'=&gt;array('data' =&gt; t('Mail'), 'field' =&gt; 'u.mail'),
    'last_access' =&gt; array('data' =&gt; t('Last Access'), 'field' =&gt; 'u.access'),
    'login_count' =&gt; array('data' =&gt; t('Login Count'),'field'=&gt;'a.count_logins'),
    'join_date'=&gt;array('data' =&gt; t('Join Date'), ),
    'post_count'=&gt;array('data' =&gt; t('Posts Count'),),
    'comment_count'=&gt; array('data' =&gt; t('Comment Count'),)
  );


then I created a query for this:

$query = db_select('users','u');
  $query = $query-&gt;extend('PagerDefault')-&gt;extend('TableSort');
  $query-&gt;leftJoin(db_select('mycustom_report','r')-&gt;fields('r',array('count(*)    AS  count_logins','uid'))-&gt;groupBy('uid'),'a','a.uid=u.uid');
  $uids = $query
    -&gt;fields('u',array('uid','name','mail','access'))-&gt;fields('a',array('count_logins'))-&gt;where('u.uid!=0')
    -&gt;limit(50)
    -&gt;orderByHeader($header)
    -&gt;execute()
    -&gt;fetchAll();


this query gives me this error 

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'r.countAScount_logins'


any ways to create count field with db_select method in drupal? db_query() seems does not support ->orderByHeader() method 

my full query which works fine looks like:

select u.uid,u.created,u.name,u.mail,u.access,counted_login,counted_post,counted_comment 
  from users as u 
          left join (select r.uid, count(*) as counted_login  from mycustom_report as r group by r.uid ) as d on u.uid = d.uid 
          left join (select r.uid, count(*) as counted_post from node as r group by r.uid ) as p on p.uid = u.uid
          left join (select r.uid, count(*) as counted_comment from comment as r group by r.uid ) as c on c.uid = u.uid;
This error simply means that column does not exist in your database which means u have to write addExpression() method for solving these problem as follows and even you have not joined the table properly use your query as below

$query = db_select('users','u');
$query = $query-&gt;extend('PagerDefault')-&gt;extend('TableSort');
$query-&gt;leftJoin('mycustom_report','r','r.uid=u.uid');
$query-&gt;fields('r');
$query-&gt;fields('u');
$query-&gt;addExpression('COUNT(uid)', 'uid_count');
$result = $query-&gt;execute()-&gt;fetchAll();


this will surely removes your error the main issue with your query is that you are combining the db_query syntax with db_select