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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Rory Campbell-Lange | 2002-05-23 23:24:47 | Re: auto update dates |
Previous Message | Ron Johnson | 2002-05-23 22:54:21 | Re: COPY returns Bad timestamp external rep.. |