Search Postgresql Archives

Help me with this multi-table query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux