Thread: Help me with this multi-table query
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;
This gives me the output like this -
uid | nc | cc
-----+-------+-------
1 | 14790 | 14790
4 | 2684 | 2684
19 | 1170 | 1170
24 | 80 | 80
29 | 119 | 119
33 | 64 | 64
36 | 9 | 0
41 | 78 | 78
42 | 7 | 0
43 | 2 | 0
44 | 2 | 2
50 | 2 | 0
55 | 0 | 0
58 | 0 | 0
60 | 0 | 0
73 | 0 | 0
75 | 0 | 0
76 | 0 | 0
81 | 0 | 0
82 | 0 | 0
85 | 0 | 0
86 | 0 | 0
88 | 0 | 0
90 | 0 | 0
91 | 0 | 0
92 | 0 | 0
93 | 0 | 0
94 | 0 | 0
95 | 0 | 0
(29 rows)
Whereas, the output for the individual count queries -
1. select u.uid, count(n.nid) nc from users u left join node n on (
n.uid = u.uid ) group by u.uid having u.uid <> 0 order by u.uid;
2. select u.uid, count(c.nid) cc from users u left join comments c on (
c.uid = u.uid ) group by u.uid having u.uid <> 0 order by u.uid;
are as follows -
uid | nc
-----+-----
1 | 174
4 | 61
19 | 65
24 | 20
29 | 17
33 | 16
36 | 9
41 | 26
42 | 7
43 | 2
44 | 2
50 | 2
55 | 0
58 | 0
60 | 0
73 | 0
75 | 0
76 | 0
81 | 0
82 | 0
85 | 0
86 | 0
88 | 0
90 | 0
91 | 0
92 | 0
93 | 0
94 | 0
95 | 0
(29 rows)
uid | cc
-----+----
1 | 85
4 | 44
19 | 18
24 | 4
29 | 7
33 | 4
36 | 0
41 | 3
42 | 0
43 | 0
44 | 1
50 | 0
55 | 0
58 | 0
60 | 0
73 | 0
75 | 0
76 | 0
81 | 0
82 | 0
85 | 0
86 | 0
88 | 0
90 | 0
91 | 0
92 | 0
93 | 0
94 | 0
95 | 0
(29 rows)
Something is seriously wrong.
I want nc and cc in just one query. How to ?
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
On 23 March 2010 11:07, Nilesh Govindarajan <lists@itech7.com> 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
On 03/24/2010 12:45 AM, Dean Rasheed wrote: > On 23 March 2010 11:07, Nilesh Govindarajan<lists@itech7.com> 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
On 24 March 2010 05:17, Nilesh Govindarajan <lists@itech7.com> wrote: > On 03/24/2010 12:45 AM, Dean Rasheed wrote: >> >> On 23 March 2010 11:07, Nilesh Govindarajan<lists@itech7.com> 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 ? > Well the problem with the original joined query is that when it is executed there will be an intermediate step where it has to consider many thousands of rows (one for each combination of a node and comment for each user). You can see the number of rows processed from your original query by adding up the counts (about 17000). This problem would be compounded if you added more table joins and counts to the query. One way to re-write it using sub-queries would be something like select v1.uid, v1.nc, v2.cc from (select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid) as v1, (select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid) as v2 where v1.uid=v2.uid order by u.uid This is the equivalent of defining a couple of views for the counts and then selecting from those views. Another possibility would be something like select u.uid, (select count(n.nid) from node n where n.uid = u.uid) as nc, (select count(c.nid) from comments c where c.uid = u.uid) as cc from users u order by u.uid There are probably other ways too. Which is best probably depends on the size and distribution of your data, and any indexes you have. You might benefit from indexes on the uid columns of node and comments, if you don't already have them. Try timing them in psql with \timing, and use EXPLAIN ANALYSE to see how each is executed. Regards, Dean
On 03/24/2010 01:14 PM, Dean Rasheed wrote: > On 24 March 2010 05:17, Nilesh Govindarajan<lists@itech7.com> wrote: >> On 03/24/2010 12:45 AM, Dean Rasheed wrote: >>> >>> On 23 March 2010 11:07, Nilesh Govindarajan<lists@itech7.com> 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 ? >> > > Well the problem with the original joined query is that when it is > executed there will be an intermediate step where it has to consider > many thousands of rows (one for each combination of a node and comment > for each user). You can see the number of rows processed from your > original query by adding up the counts (about 17000). This problem > would be compounded if you added more table joins and counts to the > query. > > One way to re-write it using sub-queries would be something like > > select v1.uid, v1.nc, v2.cc from > (select u.uid, count(n.nid) nc from users u left join node n on ( > n.uid = u.uid ) group by u.uid) as v1, > (select u.uid, count(c.nid) cc from users u left join comments c on ( > c.uid = u.uid ) group by u.uid) as v2 > where v1.uid=v2.uid > order by u.uid > > This is the equivalent of defining a couple of views for the counts > and then selecting from those views. > > Another possibility would be something like > > select > u.uid, > (select count(n.nid) from node n where n.uid = u.uid) as nc, > (select count(c.nid) from comments c where c.uid = u.uid) as cc > from users u order by u.uid > > There are probably other ways too. Which is best probably depends on > the size and distribution of your data, and any indexes you have. You > might benefit from indexes on the uid columns of node and comments, if > you don't already have them. Try timing them in psql with \timing, and > use EXPLAIN ANALYSE to see how each is executed. > > Regards, > Dean The second method is the best. It takes 3.311 ms to execute. The first method suggested by you takes 5.7 ms, and the worst is my method which takes 60ms (boo). Thanks a lot :) :) :) -- Nilesh Govindarajan Site & Server Administrator www.itech7.com