pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Nilesh Govindarajan <lists@itech7.com>
To: pgsql-sql@postgresql.org
Subject: Help me with this multi-table query
Date: Tue, 23 Mar 2010 20:07:10 +0530
Message-ID: <4BA8D216.3000802@itech7.com> (raw)

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



view thread (2+ messages)  latest in thread

Message-ID: <4BA8D216.3000802@itech7.com>
Permalink:  ../4BA8D216.3000802@itech7.com/
Also on:    postgresql.org/message-id/4BA8D216.3000802@itech7.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: lists@itech7.com
  Subject: Re: Help me with this multi-table query
  In-Reply-To: <4BA8D216.3000802@itech7.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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