From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | Postgresql Novice List <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: query problem - get count in related table |
Date: | 2002-05-23 20:13:03 |
Message-ID: | 1022184783.22629.25.camel@rebel |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 |
+---------------------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Hatcher | 2002-05-23 22:40:50 | COPY returns Bad timestamp external rep.. |
Previous Message | Adam Erickson | 2002-05-23 19:06:35 | Re: when to use char, varchar or text |