On 03/24/2010 12:45 AM, Dean Rasheed wrote:
On 23 March 2010 11:07, Nilesh Govindarajan<lists@xxxxxxxxxx> wrote:
Hi,
I want to find out the userid, nodecount and comment count of the userid.
I'm going wrong somewhere.
Check my SQL Code-
select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node
n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by
u.uid having u.uid<> 0 order by u.uid;
I think you want select u.uid, count(distinct n.nid) nc ,
count(distinct c.cid) cc from ...
otherwise you're counting each node/comment multiple times as the rows in the
join multiply up (note 85 x 174 = 14790).
For big tables, this could start to become inefficient, and you might
be better off
doing your queries 1 and 2 above as sub-queries and joining them in an
outer query.
Regards,
Dean
Thanks a lot !! It worked.
How to do it using subqueries ?
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
