Re: Counting multiple tables
Sasha Pachev wrote: Gustavo Andrade wrote: I want to know if its possible to count the total records of multiple tables: Example: I have 3 tables. I want to know the total records of each table using only 1 query. Is that possible? It is actually possible with 4.0, although rather convoluted: (select count(*) from f) union (select count(*) from f1) union (select count(*) from ft1); Unfortunately, the following does not work quite right: (select count(*),'f' as table_name from f) union (select count(*),'f1' as table_name from f1) union (select count(*),'ft1' as table_name from ft1); +--++ | count(*) | table_name | +--++ |3 | f | |6 | f | |7 | f | +--++ 3 rows in set (0.00 sec) However, if you pad the names of tables which are shorter to the length of the longest one: mysql> (select count(*),'f ' as table_name from f) union (select count(*),'f1 ' as table_name from f1) union (select count(*),'ft1' as table_name from ft1); +--++ | count(*) | table_name | +--++ |3 | f | |6 | f1 | |7 | ft1| +--++ 3 rows in set (0.00 sec) Once the bug gets fixed, you would not need to pad. As I understand it, the length of the field is based on the first SELECT, so you only need to pad the first one (or put the longest one first). mysql> (SELECT COUNT(*),'t ' AS table_name FROM t) -> UNION -> (SELECT COUNT(*),'t1' AS table_name FROM t1) -> UNION -> (SELECT COUNT(*),'table1' AS table_name FROM table1); +--++ | COUNT(*) | table_name | +--++ | 14 | t | |7 | t1 | |4 | table1 | +--++ 3 rows in set (0.00 sec) mysql> (SELECT COUNT(*),'table1' AS table_name FROM table1) -> UNION -> (SELECT COUNT(*),'t' AS table_name FROM t) -> UNION -> (SELECT COUNT(*),'t1' AS table_name FROM t1); +--++ | COUNT(*) | table_name | +--++ |4 | table1 | | 14 | t | |7 | t1 | +--++ 3 rows in set (0.00 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting multiple tables
At 21:17 -0600 5/17/04, Sasha Pachev wrote: Gustavo Andrade wrote: I want to know if its possible to count the total records of multiple tables: Example: I have 3 tables. I want to know the total records of each table using only 1 query. Is that possible? It is actually possible with 4.0, although rather convoluted: (select count(*) from f) union (select count(*) from f1) union (select count(*) from ft1); Unfortunately, the following does not work quite right: (select count(*),'f' as table_name from f) union (select count(*),'f1' as table_name from f1) union (select count(*),'ft1' as table_name from ft1); +--++ | count(*) | table_name | +--++ |3 | f | |6 | f | |7 | f | +--++ 3 rows in set (0.00 sec) However, if you pad the names of tables which are shorter to the length of the longest one: mysql> (select count(*),'f ' as table_name from f) union (select count(*),'f1 ' as table_name from f1) union (select count(*),'ft1' as table_name from ft1); +--++ | count(*) | table_name | +--++ |3 | f | |6 | f1 | |7 | ft1| +--++ 3 rows in set (0.00 sec) Once the bug gets fixed, you would not need to pad. It's fixed in 4.1.1: http://dev.mysql.com/doc/mysql/en/UNION.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting multiple tables
Gustavo Andrade wrote: I want to know if its possible to count the total records of multiple tables: Example: I have 3 tables. I want to know the total records of each table using only 1 query. Is that possible? It is actually possible with 4.0, although rather convoluted: (select count(*) from f) union (select count(*) from f1) union (select count(*) from ft1); Unfortunately, the following does not work quite right: (select count(*),'f' as table_name from f) union (select count(*),'f1' as table_name from f1) union (select count(*),'ft1' as table_name from ft1); +--++ | count(*) | table_name | +--++ |3 | f | |6 | f | |7 | f | +--++ 3 rows in set (0.00 sec) However, if you pad the names of tables which are shorter to the length of the longest one: mysql> (select count(*),'f ' as table_name from f) union (select count(*),'f1 ' as table_name from f1) union (select count(*),'ft1' as table_name from ft1); +--++ | count(*) | table_name | +--++ |3 | f | |6 | f1 | |7 | ft1| +--++ 3 rows in set (0.00 sec) Once the bug gets fixed, you would not need to pad. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting multiple tables
"Dathan Vance Pattishall" <[EMAIL PROTECTED]> wrote: > SHOW TABLE STATUS to get the count of each table, then your application = > adds > the number in the Rows field from each of the tables returned. For InnoDB tables SHOW TABLE STATUS returns approximate row count. > > >> -Original Message- >> From: Gustavo Andrade [mailto:[EMAIL PROTECTED] >> Sent: Sunday, May 16, 2004 3:10 PM >> To: [EMAIL PROTECTED] >> Subject: Counting multiple tables >> I want to know if its possible to count the total records of multiple >> tables: >> Example: I have 3 tables. I want to know the total records of each = > table >> using only 1 query. >> Is that possible? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Counting multiple tables
SHOW TABLE STATUS to get the count of each table, then your application adds the number in the Rows field from each of the tables returned. DVP Dathan Vance Pattishall http://www.friendster.com > -Original Message- > From: Gustavo Andrade [mailto:[EMAIL PROTECTED] > Sent: Sunday, May 16, 2004 3:10 PM > To: [EMAIL PROTECTED] > Subject: Counting multiple tables > > I want to know if its possible to count the total records of multiple > tables: > Example: I have 3 tables. I want to know the total records of each table > using only 1 query. > > Is that possible? > > _ > Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike, > Warcraft ou outros. entre em > http://www.arena-star.com.br/forum/ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting multiple tables
I want to know if its possible to count the total records of multiple tables: Example: I have 3 tables. I want to know the total records of each table using only 1 query. Is that possible? _ Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike, Warcraft ou outros. entre em http://www.arena-star.com.br/forum/
Counting multiple tables
Hi, I'm trying to do a SELECT which then counts records in two separate tables, eg: SELECT a_id, count(b_id) FROM a LEFT JOIN b USING(a_id) merged with SELECT a_id, count(c_id) FROM a LEFT JOIN c USING(a_id) So you would end up with a_id, count(b_id), count(c_id) Showing the number of records linked in each table respectively. Is there a simple way of doing this? For what it's worth, I'm using MySQL 3.23.49 and accessing it from PHP 4. Thanks, James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
