Re: query problem - get count in related table

On Thu, 2002-05-23 at 18:14, Rory Campbell-Lange wrote:
> 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)

Ah, then you want a LEFT OUTER JOIN.
test2=# select a.id as "a.id", a.data, b.id as "b.id", count(*) as cnt
test2-# from a left outer join b on (b.id = a.id)
test2-# group by a.id, a.data, b.id;
a.id | data | b.id | cnt
------+------+------+-----
1 | 2 | 1 | 3
2 | 1 | 2 | 2
3 | 4 | 3 | 1
5 | 2 | | 1
(4 rows)

Notice the "space" in the last b.id column. That's really a
NULL "value".

You may have to write a stored procedure that populates a temp
table, since with a straight query, you will _always_ get a
count of one for all rows that return.

The inner loop of your stored procedure could say something
on the order of:
if b.id is not null then
insert into temp_table values(a.id, data, b.id, cnt);
else
insert into temp_table values(a.id, data, b.id, 0);
end if;
The exact syntax is probably wrong, since, even though I
have years of rdbms experience, postgres is new to me.

> ------------------------------------------------------------------
> 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

--
+---------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://ronandheather.dhs.org:81 |
| |
| "I have created a government of whirled peas..." |
| Maharishi Mahesh Yogi, 12-May-2002, |
! CNN, Larry King Live |
+---------------------------------------------------------+