Re: query problem - get count in related table - Mailing list pgsql-novice

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.l.johnson@cox.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.l.johnson@cox.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                                  |
+---------------------------------------------------------+