How about: select (@CNT:=count(*)) FROM table1 union select (@CNT:[EMAIL PROTECTED](*)) from table2 union select (@CNT:[EMAIL PROTECTED](*)) from table3;
If you want only the sum: (select (@CNT:=count(*)) FROM table1) union (select (@CNT:[EMAIL PROTECTED](*)) from table2) union (select (@CNT:[EMAIL PROTECTED](*)) from table3) ORDER BY 1 DESC LIMIT 1 GG > -----Original Message----- > From: Rhino [mailto:[EMAIL PROTECTED] > Sent: November 24, 2004 09:47 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: Multiple table count in one sql-query > > > ----- Original Message ----- > From: "Martin Rytz" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, November 24, 2004 8:47 AM > Subject: Multiple table count in one sql-query > > > > Hello People > > > > I should have the count from 3 tables in one query; > something like this: > > > > select count(*) from table1 union select count(*) from > table2 union select > > count(*) from table3 union; > > > > The Problem ist, that the query gives 3 results back (the count from > table1, > > table2 and table3) BUT I should have only one result, the > added value from > > all 3 tables... > > > > How can I do this within MySQL? Are there variables I could > use within the > > query? Other possibilities? > > > > Thank you for your help! > > > One approach would be to create a temporary table consisting > of one integer > column, then write the results of your query to that table. > Here is a script > that demonstrates this technique: > > #Create temporary table containing a union of the row counts > from each of > severa > create temporary table if not exists temp01 > select count(*) as total_rows > from Employee > UNION > select count(*) as total_rows > from emp > UNION > select count(*) as total_rows > from dept; > > #Display the contents of the temporary table. > select * from temp01; > > #Sum the row counts from the different tables. > select sum(total_rows) from temp01; > > #Drop the temporary table. > drop table if exists temp01; > > This script works perfectly on my 4.0.15 system. > > Rhino > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
