Thanks, Earl. I didn't think about a subquery. Nancy
________________________________ From: Earl Miles <[email protected]> To: [email protected] Sent: Tue, August 17, 2010 1:09:51 PM Subject: Re: [development] Convert query to COUNT query On 8/11/2010 12:27 PM, nan wich wrote: > 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 You can almost always do: SELECT COUNT(*) as expression FROM (SELECT ...); Drop the ORDER BY and any fields that you don't need from the query (you need the group by fields) and you'll be okay.
