Re: find the number of row for each tables

Re: find the number of row for each tables

> 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

find the number of row for each tables

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

Re: find the number of row for each tables

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 ()), count(distinct
())
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
 and  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

Maximum size before having to use BLOBs.

Hi,

What is the maximum size 'text' or  'varchar' can support before a BLOB has to be
used. I need to store some small text files and I don't know how big is too big
for a 'varchar'.

Thanks in advance
Ryan

Re: find the number of row for each tables

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 ()), count(distinct
> > ())
> > 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
> >  and  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!"
> -----------------------------------------------------------------------------

Re: [NOVICE] Maximum size before having to use BLOBs.

On Thu, 15 Jun 2000, Ryan Ho wrote:

> What is the maximum size 'text' or  'varchar' can support before a BLOB has to be
> used. I need to store some small text files and I don't know how big is too big
> for a 'varchar'.

As I understand things, a "tuple" (all the elements in a row) plus a few
bytes for book-keeping have to be less than 8192 bytes.  I haven't pushed
to see if my understanding is correct.

Gord

Matter Realisations     http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101  9504 182 St. NW    Edmonton, AB, CA  T5T 3A7
780/481-8019            ghaverla @ freenet.edmonton.ab.ca

Re: Maximum size before having to use BLOBs.

Re: find the number of row for each tables

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 ()), count(distinct
> ())
> 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
>  and  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!"   
-----------------------------------------------------------------------------

Re: Maximum size before having to use BLOBs.

duccini=> create table test (mail varchar(8192));
ERROR:  length for type 'varchar' cannot exceed 8088
duccini=> 

the answer would be 8088

-duck

On Thu, 15 Jun 2000, Ryan Ho wrote:

> Hi,
> 
> What is the maximum size 'text' or  'varchar' can support before a BLOB has to be
> used. I need to store some small text files and I don't know how big is too big
> for a 'varchar'.
> 
> Thanks in advance
> Ryan
> 


-----------------------------------------------------------------------------
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!"   
-----------------------------------------------------------------------------

Re: find the number of row for each tables

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 ()), count(distinct
> > > ())
> > > 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
> > >  and  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!"   
-----------------------------------------------------------------------------

Re: find the number of row for each tables

Try simply SELECT * from friends;


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
> 
> 
> 
> 

Yours Truly,

  	 - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -

Re: find the number of row for each tables

On Tue, 13 Jun 2000, Patrick Coulombe wrote:

> SELECT * from friends WHERE friends.f_id = strangers.s_id AND
> friends.categorie = 1
> ----------
> 80 rows

SELECT *
FROM friends F
WHERE friends.categorie = 1 AND EXISTS (SELECT *
					FROM strangers
					WHERE F.f_id = strangers.s_id);

> Can I just do 1 query and be able to find the number of row for each tables?

We have to use UNION.

Jesus.