<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt;color:#7f003f;"><DIV></DIV>
<DIV>I'm havng trouble getting my head around converting this to a count query for doing a pager_query.</DIV>
<DIV> </DIV>
<DIV><FONT face="Courier New, courier, monaco, monospace, sans-serif" color=#000000>SELECT c.name, c.mail, COUNT(*) AS num, pv.value AS usertype, td.name AS role<BR> FROM {comments} c <BR> INNER JOIN {node} n ON n.nid=c.nid<BR> INNER JOIN {term_node} tn ON tn.nid=n.nid AND tn.vid=n.vid<BR> INNER JOIN {term_data} td ON td.tid=tn.tid AND td.vid = 2<BR> LEFT JOIN {profile_values} pv ON pv.uid=c.uid AND pv.fid = 13<BR> WHERE c.mail <> ''<BR> GROUP BY role, c.mail ORDER BY num DESC<BR></FONT> </DIV>
<DIV>I know the pv.value (usertype, LEFT JOIN profile_values) is not needed. But a user (c.mail) will end up in multiple roles (<FONT color=#000000>td,name</FONT>, this is not the same as a Drupal role), so DISTINCT is my real downfall here.</DIV>
<DIV> </DIV>
<P><FONT face="bookman old style, new york, times, serif" color=#ff007f size=4><EM><STRONG>Nancy</STRONG></EM></FONT></P></div></body></html>