[development] Convert query to COUNT query

[nan wich]

I'm havng trouble getting my head around converting this to a count query for 
doing a pager_query.

SELECT c.name, c.mail, COUNT(*) AS num, pv.value AS usertype, td.name AS role
 FROM {comments} c 
 INNER JOIN {node} n ON n.nid=c.nid
 INNER JOIN {term_node} tn ON tn.nid=n.nid AND tn.vid=n.vid
 INNER JOIN {term_data} td ON td.tid=tn.tid AND td.vid = 2
 LEFT JOIN {profile_values} pv ON pv.uid=c.uid AND pv.fid = 13
 WHERE c.mail <> ''
 GROUP BY role, c.mail ORDER BY num DESC
 
I know the pv.value (usertype, LEFT JOIN profile_values) is not needed. But a 
user (c.mail) will end up in multiple roles (td,name, this is not the same as a 
Drupal role), so DISTINCT is my real downfall here.

Nancy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20100811/54e9fe0b/attachment.html