Re: query problem - get count in related table

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>