Обсуждение: 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
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 -
> 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
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.
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
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
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
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!" -----------------------------------------------------------------------------
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!" > -----------------------------------------------------------------------------
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!" -----------------------------------------------------------------------------
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!" -----------------------------------------------------------------------------