Thread: query problem - get count in related table
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>
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 | +---------------------------------------------------------+
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)
Cheers!
Rory
------------------------------------------------------------------
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
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
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 |
+---------------------------------------------------------+
On Fri, 24 May 2002 00:14:59 +0100, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:
>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)
>
>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)
Rory,
assuming all your a.id are NOT NULL and unique:
SELECT a.id, a.data, count(b.id)
FROM a LEFT JOIN b ON a.id = b.id
GROUP BY a.id, a.data;
HTH.
Servus
Manfred