<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt"><DIV></DIV>
<DIV>Thanks, Earl. I didn't think about a subquery.<BR> </DIV>
<P><FONT face="bookman old style, new york, times, serif" color=#ff007f size=4><EM><STRONG>Nancy</STRONG></EM></FONT></P>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial, helvetica, sans-serif"><BR>
<DIV style="FONT-SIZE: 10pt; FONT-FAMILY: arial, helvetica, sans-serif"><FONT face=Tahoma size=2>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Earl Miles <merlin@logrus.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> development@drupal.org<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Tue, August 17, 2010 1:09:51 PM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [development] Convert query to COUNT query<BR></FONT><BR>On 8/11/2010 12:27 PM, nan wich wrote:<BR>> I'm havng trouble getting my head around converting this to a count<BR>> query for doing a pager_query.<BR>> <BR>> SELECT <A href="http://c.name/" target=_blank>c.name</A>, c.mail, COUNT(*) AS num, pv.value AS usertype, <A href="http://td.name/" target=_blank>td.name</A> AS<BR>> 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><BR>You can almost always do:<BR><BR>SELECT COUNT(*) as expression FROM (SELECT ...);<BR><BR>Drop the ORDER BY and any fields that you don't need from the query (you<BR>need the group by fields) and you'll be okay.<BR></DIV></DIV></div></body></html>