pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp me with this multi-table query
2+ messages / 2 participants
[nested] [flat]
* Help me with this multi-table query
@ 2010-03-23 14:37 Nilesh Govindarajan <lists@itech7.com>
0 siblings, 1 reply; 2+ messages in thread
From: Nilesh Govindarajan @ 2010-03-23 14:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help me with this multi-table query
@ 2010-03-26 06:45 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Nilesh Govindarajan <lists@itech7.com>
0 siblings, 0 replies; 2+ messages in thread
From: A. Kretschmer @ 2010-03-26 06:45 UTC (permalink / raw)
To: pgsql-sql
In response to Nilesh Govindarajan :
> Hi,
>
> I want to find out the userid, nodecount and comment count of the userid.
>
> I'm going wrong somewhere.
>
> Check my SQL Code-
Check my example:
test=*# select * from u;
id
----
1
2
3
(3 rows)
test=*# select * from n;
uid
-----
1
1
1
2
2
3
(6 rows)
test=*# select * from c;
uid
-----
1
2
2
3
3
3
(6 rows)
test=*# select foo.id, foo.n_count, count(c) from (select u.id, count(n)
as n_count from u right join n on (n.uid=u.id) group by 1) foo left join
c on foo.id=c.uid group by id, n_count;
id | n_count | count
----+---------+-------
1 | 3 | 1
2 | 2 | 2
3 | 1 | 3
(3 rows)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-03-26 06:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-23 14:37 Help me with this multi-table query Nilesh Govindarajan <lists@itech7.com>
2010-03-26 06:45 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox