Re: query problem - get count in related table

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:
> Thanks to everyone on the list I'm up and running with postgres.
>
> I'm having trouble doing a query:
>
> table a          |    table b
> ------------------------------
>     id     data  |        id
> ------------------------------
>     1       2     |        2
>     2      1     |        2
>     3      4     |        1
>                  |        1
>                  |        1
>                  |        3
>
> 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
>
> Thanks for any help.
> Rory
>
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
+---------------------------------------------------------+
| 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                                  |
+---------------------------------------------------------+