Skip site navigation (1) Skip section navigation (2)

Re: query problem - get count in related table

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
Cc: Postgresql Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: query problem - get count in related table
Date: 2002-05-23 23:14:59
Message-ID: 20020523231458.GA15332@campbell-lange.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Ron

Thanks very much for your mail - apologies for the vagueness of my
original query. Your snippet works pretty well (although a.data has to
be specified in the group by, it appears). However I'm still looking to
get a result like this:

		 id | data | count 
		----+------+-------
		  1 |    2 |     3
		  2 |    1 |     2
		  3 |    4 |     1
		  5 |    2 |     0
		(3 rows)

Cheers!
Rory

------------------------------------------------------------------
progress so far:

brandf=# select * from a;    brandf=# select * from b;
 id | data                    id 
----+------                  ----
  1 |    2                     2
  2 |    1                     2
  3 |    4                     1
  5 |    2                     1
(4 rows)                       1
                               3
                             (6 rows)                 


brandf=# select a.id, a.data, count(*) from a,b where b.id = a.id 
		 group by a.id, a.data;

		 id | data | count 
		----+------+-------
		  1 |    2 |     3
		  2 |    1 |     2
		  3 |    4 |     1
		(3 rows)


On 23/05/02, Ron Johnson (ron(dot)l(dot)johnson(at)cox(dot)net) wrote:
> Your email is pretty vague, but maybe this is what you want:
> 
> select a.id, a.data, count(*) as cnt
> from a, b
> where b.id = a.data
> group by a.id = a.data;
 
> On Thu, 2002-05-23 at 12:31, Rory Campbell-Lange wrote:
...
> > I'd like to run a query that returned the following results:
> > 
> >  a.id  a.data  count(b.id where b.id = a.data)
> >  ---------------------------------------------
> >  1     2       2
> >  2     1       3
> >  3     4       0
-- 
Rory Campbell-Lange 
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>

In response to

Responses

pgsql-novice by date

Next:From: Rory Campbell-LangeDate: 2002-05-23 23:24:47
Subject: Re: auto update dates
Previous:From: Ron JohnsonDate: 2002-05-23 22:54:21
Subject: Re: COPY returns Bad timestamp external rep..

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group