Re: find the number of row for each tables - Mailing list pgsql-novice

From D. Duccini
Subject Re: find the number of row for each tables
Date
Msg-id Pine.GSO.4.03.10006150923290.13005-100000@ra.bpsi.net
Whole thread Raw
In response to Re: find the number of row for each tables  (NRonayette <r97m10@socotec.fr>)
List pgsql-novice
i'm sure the REAL information you want is in a system table somewhere that
could be queried of the variety

select sy_rowcount from sys.tablename where sy_tablename = 'blah';

On Thu, 15 Jun 2000, NRonayette wrote:

> Hi,
>
> You didn't made any link between your two tables, so this make a
> cartesian product, that why it is very long, and you have to cancel it.
>
> Re-try your select with the exact join condition between your 2 tables
>
> Something like :
> select count(a.id), count(b.id) from accounts a, subaccounts b
> where a.id = b.a_id;
>
> (if a_id is the id of accounts in subaccounts)
>
> (sorry for my poor english)
>
> Nicolas
>
>
> D. Duccini wrote:
> >
> > Interesting, I just tried the following:
> >
> > actmgr=> select count(a.id), count(b.id) from accounts a, subaccounts b;
> > ^C
> > CANCEL request sent
> > ERROR:  Query was cancelled.
> > actmgr=> select count(id) from accounts;
> > count
> > -----
> >   781
> > (1 row)
> >
> > actmgr=> select count(id) from subaccounts;
> > count
> > -----
> >  1939
> > (1 row)
> >
> > i had to cancel the first one after about a minute, but the second two
> > came right back
> >
> > since
> >
> > actmgr=> select count(distinct a.id), count(distinct b.id) from accounts a, subaccounts b;
> >
> > this doesn't work.
> >
> > -duck
> >
> > On Thu, 15 Jun 2000, NRonayette wrote:
> >
> > > Hi everybody,
> > > i try something that could work (i tested it on a Oracle base, and it
> > > works fine, sorry, i haven't got postgresql at my office :-((
> > >
> > > select count(distinct (<Primary_key_of_friends>)), count(distinct
> > > (<Primary_key_of_strangers>))
> > > from Friends, Strangers
> > > WHERE friends.f_id = strangers.s_id AND friends.categorie = 1;
> > >
> > > If f_id and S_id are the primary key of each table, replace
> > > <primary_key_of_friend> and <primary_key_of_strangers> by f_id and s_id
> > > respectively.
> > >
> > > i hope this will work for you
> > >
> > > Nicolas
> > >
> > > Patrick Coulombe wrote:
> > > >
> > > > > Try simply SELECT * from friends;
> > > > ???
> > > > Not working... I just want to know if it's possible to know how many rows
> > > > the query return for each table when i do a query with two table ie: select
> > > > * from table1, table2 where...
> > > >
> > > > can i know the number of row for table1 and the number of row for table2
> > > > without do 2 querys.
> > > >
> > > > thank you
> > > > hope to be more clear this time
> > > > patrick
> > > >
> > > > > On Tue, 13 Jun 2000, Patrick Coulombe wrote:
> > > > >
> > > > > > hi,
> > > > > > here's the question : if I do this query on my database :
> > > > > >
> > > > > > SELECT * from friends, strangers WHERE friends.f_id = strangers.s_id AND
> > > > > > friends.categorie = 1
> > > > > > ----------
> > > > > > 88 rows
> > > > > >
> > > > > > and I need to do this query to know how many rows just for my table
> > > > friends
> > > > > > (not both table) :
> > > > > >
> > > > > > SELECT * from friends WHERE friends.f_id = strangers.s_id AND
> > > > > > friends.categorie = 1
> > > > > > ----------
> > > > > > 80 rows
> > > > > >
> > > > > >
> > > > > > Can I just do 1 query and be able to find the number of row for each
> > > > tables?
> > > > > > Hope to be understand...
> > > > > >
> > > > > > Patrick
> > >
> >
> > -----------------------------------------------------------------------------
> > david@backpack.com            BackPack Software, Inc.        www.backpack.com
> > +1 651.645.7550 voice       "Life is an Adventure.
> > +1 651.645.9798 fax            Don't forget your BackPack!"
> > -----------------------------------------------------------------------------
>


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.
+1 651.645.9798 fax            Don't forget your BackPack!"
-----------------------------------------------------------------------------


pgsql-novice by date:

Previous
From: "D. Duccini"
Date:
Subject: Re: find the number of row for each tables
Next
From: "Fred Lecul"
Date:
Subject: problems with table