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(at)campbell-lange(dot)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(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 |
+---------------------------------------------------------+